Exclude records - SQL Server Forums

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)

Exclude records

Author

Topic

cardgunner

326 Posts

Posted - 2007-11-15 : 11:22:18

I'm exhausted thinking about this.

Table A
ID item
a 2
b 3
c 1
d 6

Table B

ID Ref
a
b
c
d b

I want to my results to be

Query AB

ID Item
a 2
c 1

so I'm excluding d because ref b and b because it was in the ref column of Table B.

Make sense?

Card Gunner

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-15 : 11:37:25

select ta.*
from ta
left outer join
(select Id, Ref from tb where Ref is not null) tb
on ta.ID = tb.ID or ta.ID = tb.Ref
where tb.ID is null

==========================================
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.

cardgunner

326 Posts

Posted - 2007-11-15 : 12:08:36

Wow,

Perfect!

Thank you and have a Great Thanksgiving!

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources