SELECT records from TABLE1 with no match 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 2000 Forums

Transact-SQL (2000)

SELECT records from TABLE1 with no match in TABLE2

Author

Topic

ddouma
Starting Member

10 Posts

Posted - 2007-01-30 : 09:10:40

Hi, I'm hoping that someone could help me out. Basically I need to select the rows from TABLE 1, that don't have a match in TABLE 2 where ENV_ID = 43. So for the example data below, I'd like to return HS_ID = 5

TABLE 1
--------
HS_ID NAME
3 WKS01
4 WKS02
5 WKS03

TABLE 2
--------
ENV_ID HS_ID
40 4
40 3
43 4
43 3
40 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 09:17:46

select t1.*
from @table1 as t1
left join @table2 as t2 on t2.hs_id = t1.hs_id and t2.env_id = 43
where t2.hs_id is null

Peter Larsson
Helsingborg, Sweden

ddouma
Starting Member

10 Posts

Posted - 2007-01-30 : 09:29:00

Thanks for the quick reply Peter - that was it!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 09:32:27

[code]-- prepare sample data
declare @TABLE1 table (HS_ID int, NAME sysname)

insert @table1
select 3, 'WKS01' union all
select 4, 'WKS02' union all
select 5, 'WKS03'

declare @table2 table (env_id int, hs_id int)

insert @table2
select 40, 4 union all
select 40, 3 union all
select 43, 4 union all
select 43, 3 union all
select 40, 5

-- show the result
select t1.*
from @table1 as t1
left join @table2 as t2 on t2.hs_id = t1.hs_id
and t2.env_id = 43
where t2.hs_id is null[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources