Select only Null records from query 1

[devon59]

Hi there,
I was trying to select Not NULL record from below query but it did not return anything. Could anyone please help? Basically, I want to get only records the people that did not order.

Thank you so much.

SELECT result1.*
FROM
(SELECT tbl1.Mail, tbl2.Order

FROM tbl1

left outer join tbl2
on tbl1.ID=tbl2.ID

) as Result1

WHERE result1.Order = Null

 

[SQLDenis]

WHERE result1.Order IS Null
instead of
WHERE result1.Order = Null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

why not this?

SELECT tbl1.Mail, tbl2.[Order]
FROM tbl1
left outer join tbl2
   on tbl1.ID=tbl2.ID
where tbl2.ID is null

btw order is a keyword you need to do [order]

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[devon59]

hmm... it still doesn return anything. Maybe my query is wrong. Let me explain into more detail. Basically, I have 2 files, one has all records that I mailed to and another has only people that responded. I want to join the 2 table but select only people that did not respond. How do I do that?

Thank you very much for your help.

 

[SQLDenis]

take a look at this

CREATE TABLE #Mail (ID INT primary key)
INSERT INTO #Mail VALUES (1)
INSERT INTO #Mail VALUES (2)
INSERT INTO #Mail VALUES (3)


CREATE TABLE #Order (ID INT primary key,[Order] varchar(50))
INSERT INTO #Order VALUES (1,'Wasabi')
INSERT INTO #Order VALUES (3,'Ketchup')



SELECT * FROM #Mail j
LEFT OUTER JOIN #Order n ON n.ID = j.ID
WHERE n.ID IS  NULL

SELECT * FROM #Order n
RIGHT OUTER JOIN #Mail j  ON n.ID = j.ID
WHERE n.ID IS  NULL

drop table #Mail,#Order

there are 3 mailid's but only 2 orderid's orderid 2 will be returned with either the left or right join

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[devon59]

Thank you so much!!! That worked!