Select statement
-
Hi everybody!
I have 2 tables. One containing the suppliers and one containing the users for each supplier. I tryed to create a select statement which is bringing me only the suppliers that have no users.
SELECT suppliers.supplierid, users.supplierid,
suppliers.suppliercode
FROM suppliers LEFT OUTER JOIN
users ON suppliers.supplierid = users.supplierid
So, the suppliers that have no user attached will have on the users.supplierid column the value = null. But I need only the suppliers that have the value null in users.suppliers
How do I write this condition? I tryed some ideas but didn't worked. Can anybody help me?
Thanks a lot,
Durug
-
One solution that minimally changes your approach:
SELECT suppliers.supplierid, users.supplierid,
suppliers.suppliercode
FROM suppliers LEFT OUTER JOIN
users ON suppliers.supplierid = users.supplierid
where users.supplierid is null -- disregard any supplier with existing users
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
-
Thanks!
I was using where supplierid = null.
Durug
-
You can not use '= null' only 'Is null'
-
=NULL is possible if you set the server to ANSI_NULLS OFF. (Through SET Statement, sp_DBOption, or ALTER DATABASE).
The Problem is it is not ANSI92 Compliant. It is better always to use ANSI standard.
(NULL means unknown. So, when 2 unknown are compared the answer should be again unknown.)
For more information check BOL
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
quote:
=NULL is possible if you set the server to ANSI_NULLS OFF. (Through SET Statement, sp_DBOption, or ALTER DATABASE).Thanks for the info! I was under impression, that since SQL7 '=NULL' not usable anymore.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply