Returning values if a field is not in a column

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 2000 Forums

SQL Server Development (2000)

Returning values if a field is not in a column

Author

Topic

jonhath
Starting Member

13 Posts

Posted - 2007-11-15 : 12:07:46

I'm trying to write a query that will return a person's information if their ID is -not- in a column in a different table.

For example:

IDCheck
001
003
005


IDNumber   FirstName   LastName   
001        Bob         Jones
002        Jill        Smith
003        Jon         Doe
004        Jeff        Miller
005        Harry       Douglas

The output I want would be:


IDNumber   FirstName   LastName 
002        Jill        Smith
004        Jeff        Miller

Because their IDs are not in the IDCheck table.

The actual implementation is a lot more complex and the IDCheck table is dynamically generated. If someone could point me in the right direction I'd really appreaciate it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 12:17:03

SELECT t1.*
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.IDNumber = t2.IDCheck
WHERE t2.IDCheck IS NULL

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources