Newbie: NOT EXIST or NOT IN?

• James Red

  Grasshopper

  Points: 10

  More actions

  September 7, 2007 at 12:59 pm

  #374145

  As a newcomer to T-SQL, I've been amazed at both the power, albeit cryptic, of T-SQL. 

  To my point, when clients and coworkers send me a list of orders and ask for status, I just do something like this:

  SELECT col_number, col_status

  FROM tbl_orders

  WHERE col_number IN ('123', '456', '246')

  and so on.  I've noticed that sometimes the requested orders list includes some that don't exist because they are mistyped or not in the system or whatever.  Is there a way to return a result/row for every item in the IN() even if it doesn't exist in the db?

  Thanks!

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  September 7, 2007 at 1:23 pm

  #732255

  Move your search criteria into a "table" left join from it to your tbl_orders as below ...

   

  SELECT cols.col_number, col_status

  FROM

      (SELECT '123' AS col_number

        UNION

       SELECT '456'

        UNION

       SELECT '246') AS cols

   

      LEFT JOIN tbl_orders AS o

          ON cols.col_number = o.col_number

  Anything with a null col_status doesn't exist.

  <EDIT>Now this isn't very flexible. To actually use this with a variable list of values, look up "SPLIT" on this site. Use that function to return a table set in place of the cols section above.

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• James Red-480275

  SSC Rookie

  Points: 39

  More actions

  September 14, 2007 at 9:00 am

  #733804

  Sorry it has taken a week to reply.

  Thank you VERY much with your help on this. It works great! Well, with UNION table thing. Now I'm going to look into the SPLIT thing.

  Thanks again!