Neither in Table1 nor in table2

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

SQL Server 2005 Forums

Transact-SQL (2005)

Neither in Table1 nor in table2

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2008-05-05 : 11:42:03

Hi,

How do you express neither no in TSQL. I am trying to create a view that gathers work orders that are neither in one table nor in another
I tried the following few appraoches but feel there must be something better. thanks!

1.
Select *
FROM xyz
WHERE Status = Open
AND (Number
NOT IN (SELECT WONumber
FROM dbo.Table1)
OR Number
NOT IN (SELECT WONumber
FROM dbo.Table2))
2.
Select *
FROM xyz
WHERE Status = Open
AND Number
NOT IN (SELECT WONumber
FROM dbo.Table1)
UNION
Select *
FROM xyz
WHERE Status = Open
AND Number
NOT IN (SELECT WONumber
FROM dbo.Table2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:47:04

[code]SELECT t.*
FROM xyz t
LEFT JOIN dbo.Table1 t1
ON t1.WONumber=t.Number
LEFT JOIN dbo.Table2 t2
ON t2.WONumber=t.Number
WHERE t.Status = 'Open'
AND t1.WONumber IS NULL
AND t2.WONumber IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources