I have this query there I want get all empids' who didn't not handle orders on February 12 , 2008
Select Distinct E.empid, lastname, firstname
From HR.Employees as E
INNER JOIN Sales.Orders as O
ON E.empid = O.empid
Where O.orderdate <> Convert(datetime,'02/12/2008',101)
using NOT IN, EXISTS or set operators is not applicable can somebody get me a genuine solution and explanation also
One way to do this is to use a left outer join:
Select Distinct E.empid, lastname, firstname
From HR.Employees as E left outer join
Sales.Orders as O
ON E.empid = O.empid and O.orderdate = Convert(datetime,'02/12/2008',101)
where o.orderdate is NULL
If there is a match between employees and orders on the date, then the where clause ignores those records.
This query is rephrasing your logic. You say "I want get all empids' who didn't not handle orders on February 12 , 2008". The way this works is it actually matches the employees to orders on that date. However, because it uses a left outer join, the match leaves a NULL when there is no match. These are the ones that you want.
In a sense, this query answers the equivalent question, phrased as "I want all empids that fail to match an order on such-and-such a date."
For Sql-Server:
I think the problem is your date format. Try using ISO format (yyyymmdd) instead. Also since you are not having any time on the right, your o.orderdate should be a date type field otherwise convert it as convert(date,O.orderdate) <> Convert(date,'20081202');
Select Distinct E.empid, lastname, firstname
From HR.Employees as E
INNER JOIN Sales.Orders as O
ON E.empid = O.empid
Where convert(date,O.orderdate) <> Convert(date,'20081202')
Select E.empid, lastname, firstname
From HR.Employees as E
WHERE NOT EXISTS (SELECT O.Empid FROM Sales.Orders as O
WHERE O.empid = E.EmpID
AND O.orderdate = '02/12/2008')
EDIT: The assumption is that the Orders table will not have records of a given date for employees who didn't handle it.
Select E.empid, lastname, firstname, MAX(O.OrderDate)
From HR.Employees as E LEFT JOIN Sales.Orders as O
ON O.empid = E.EmpID
WHERE O.orderdate = '02/12/2008'
GROUP BY E.empid, lastname, firstname
HAVING MAX(O.OrderDate) IS NULL
EDIT2: I haven't tried this. I hope you get the idea of what is being done using LEFT JOIN, MAX and HAVING.
While doing only date comparisons you should use trunc. Try this
Select Distinct E.empid, lastname, firstname
From HR.Employees as E
INNER JOIN Sales.Orders as O
ON E.empid = O.empid
Where trunc(O.orderdate) <> trunc(TO_DATE('02/12/2008','MM/DD/YYYY'))
