Left join to get items not in right table

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

Transact-SQL (2000)

Left join to get items not in right table

Author

Topic

mparent
Starting Member

2 Posts

Posted - 2008-04-10 : 17:35:01

I have two tables with a related ID field in each. I want to find items in table A without an item in table B.

I'm using this construct:

Select A.ID, A.OTHERFIELDS, B.ID, B.OTHERFIELDS
From A left join B
On A.ID = B.ID and B.ID is null
Where A.OTHERFIELDS Criteria and B.OTHERFIELDS Criteria

My result always has no records. Any ideas ?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-10 : 17:42:48

declare @t1 table (c1 int, c2 varchar(200))
declare @t2 table (c1 int, c2 varchar(200))

insert into @t1
select 1, 'Table 1' union all
select 2, 'Table 1' union all
select 3, 'Table 1' union all
select 4, 'Table 1' union all
select 5, 'Table 1'

insert into @t2
select 1, 'Table 2' union all
select 3, 'Table 3' union all
select 5, 'Table 4'

select t1.c1, t1.c2, t2.c1, t2.c2
from @t1 t1 left join @t2 t2 on t1.c1=t2.c1
where t2.c1 is null
-- NOTE t2 columns are null so you can not have criteria unless you are looking for nulls which is not needed.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 17:44:53

Select A.ID, A.OTHERFIELDS, B.ID, B.OTHERFIELDS
From A
left join B On A.ID = B.ID
and B.OTHERFIELDS Criteria
Where A.OTHERFIELDS Criteria
and.b.id is null

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 04:53:57

or

select columns from table1 t1 where no exists(select * from table2 where id=t1.id and ...)

Madhivanan

Failing to plan is Planning to fail

mparent
Starting Member

2 Posts

Posted - 2008-04-11 : 13:12:35

Thanks for this suggestion. It works for me, and just as importantly, I understand it !!

Cheers.

quote:
Originally posted by Peso

Select A.ID, A.OTHERFIELDS, B.ID, B.OTHERFIELDS
From A
left join B On A.ID = B.ID
and B.OTHERFIELDS Criteria
Where A.OTHERFIELDS Criteria
and.b.id is null

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources