Why my query does not return correct data (NORTHWIND)?

Question

select * 
from orders o
WHERE EXISTS 
(
  select 1 
  from orders o2 
  where o2.EmployeeID = o.EmployeeID  
    AND YEAR(o2.OrderDate) = 1997 
    and MONTH(o2.OrderDate) = 9 
    AND DAY(o2.OrderDate) = 5
)

I expect to receive only two records in result instead of 228. As you can see this is EXISTS on the same table. Thank you in advance.

Answer 1

There are two orders with an orderdate=1997-09-05. One for employee=4 and one for employee=7. Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table. That totals to 228. Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )

---

Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'

Answer 2

Seems like you are using two tables based on this code: o2.EmployeeID = o.EmployeeID.

you need to use JOIN for you to be able to get the correct data.