CODEWITHSUNDEEP
sqlcompareequals
mr. Holiday
mr. Holiday

Reputation: 1800

not equal operator doesn't return correct result sql

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

Upvotes: 0

Views: 174

Answers (4)

Abhishek kumar
Abhishek kumar

Reputation: 2676

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

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.

Upvotes: 0

Kaf
Kaf

Reputation: 33829

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

Upvotes: 0

Related Questions