Select rows from a TBL that aren't in another TBL

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

Select rows from a TBL that aren't in another TBL

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-10-07 : 10:32:26

Here's my schema:

--------------------------------------------------------
tblOrders
--------------------------------------------------------
OrderID | ConfirmationNumber
--------------------------------------------------------

--------------------------------------------------------
tblTransactions
--------------------------------------------------------
TransactionID | ConfirmationNumber
--------------------------------------------------------

I want to select all the records from tblOrders that don't have a confirmation number in tblTransactions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 10:40:20

select * from tblOrders as orders
where not exists(select * from tblTransactions where ConfirmationNumber=orders.ConfirmationNumber)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 11:59:21

or use left join

select o.* from tblOrders as orders o
left join tblTransactions t
on t.ConfirmationNumber=o.ConfirmationNumber
where t.ConfirmationNumber is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources