Post reply

Select statement

  • April 16, 2002 at 11:09 am

    #78299

    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

  • April 16, 2002 at 11:47 am

    #427280

    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

  • April 16, 2002 at 12:53 pm

    #427287

    Thanks!

    I was using where supplierid = null. 🙁

    Durug

  • April 16, 2002 at 1:33 pm

    #427291

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

  • April 17, 2002 at 5:01 am

    #427318

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

  • April 17, 2002 at 7:11 am

    #427326

    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