Getting records not related to other table's

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)

Getting records not related to other table's

Author

Topic

sedwick1024
Starting Member

6 Posts

Posted - 2007-01-26 : 19:06:52

I'm trying to get records out of one table whose primary key values are not in the records of another table. As a simplified example, let's say I have a Books table that has BookID and Author columns. There are many Books records with the same Author. A Publishers table includes a BookID column, and there is a Publishers record for every published book. Unpublished books have no records in Publishers, and it's these records I want to Select given a specified Author, keeping in mind some books by the same author may be published. What query can I use to get those Books records of a certain Author whose BookIDs aren't in the BookID column of any Publishers records? Many thanks...

--Sedwick

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-26 : 20:38:36

select b.*
from books b
where b.bookid not in (select bookid from publishers)
and b.author = 'xxxxx'

select b.*
from books b
left join publishers p
on p.bookid = b.bookid
where p.bookin is null
and b.author = 'xxxxx'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sedwick1024
Starting Member

6 Posts

Posted - 2007-02-01 : 11:25:57

That seemed to work great. Thanks!

--Sedwick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources