1
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.

3

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'
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.