Select statement

  • DuruG

    SSCrazy

    Points: 2029

    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

  • sarmistead

    SSCommitted

    Points: 1671

    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

  • DuruG

    SSCrazy

    Points: 2029

    Thanks!

    I was using where supplierid = null. 🙁

    Durug

  • Eve

    SSCrazy

    Points: 2480

    You can not use '= null' only 'Is null'

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    =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 Kulasingham

    http://preethiviraj.blogspot.com/

  • Eve

    SSCrazy

    Points: 2480

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply