SQL Query to show an ID that is not in DB? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query to show an ID that is not in DB?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-10-24 : 13:32:05

I'm not sure how to write this query. I have a list of OrderIDs and I'd like SQL to show me the ones that do not appear in the DB.

Lets say that my table looks like this:
id	orderId	User	Item
1	22	joe	potatoes
2	23	Steve	Apples
3	24	Steve	Bananas
4	38	Dave	Carrots

And the query looks like this:

SELECT orderID FROM sales WHERE orderID IN ('22','51','38')

You'll notice that orderID 51 is not in the table. How do I make it tell me the orderIDs that do not appear?

-Sergio
I use Microsoft SQL 2008

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-10-24 : 14:12:56

Maybe this:


select a.id
  from (          select '22' as orderid
        union all select '51' as orderid
        union all select '51' as orderid
       ) as a
 where not exists (select * from sales where sales.orderid=a.orderid)

or this:


select a.id
  from (          select '22' as orderid
        union all select '51' as orderid
        union all select '51' as orderid
       ) as a
       left outer join sales as b
                    on b.orderid=a.orderid
 where b.orderid is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources