SQL query for missing data - 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

Transact-SQL (2000)

SQL query for missing data

Author

Topic

jpogorman
Starting Member

2 Posts

Posted - 2007-10-11 : 10:33:56

Hi,

This may or may not be basic but I can't get my head around it.

I want to select from table1 all the ids for a particular userid that are not contained in table2...

table1
id, userid

table2
fkid

For example,
table1
id, userid
1, user1
2, user1
3, user2

table2
fkid
1
3

I want to select all ids for user1 that are not contained in table 2 so I would get id 2.

I hope that is understandable.

Thank you,
JP

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 10:52:05

[code]-- Prepare sample data
declare @t1 table
(
id int,
userid varchar(20)
)

declare @t2 table
(
fkid int
)

insert @t1
select 1, 'user1' union all
select 2, 'user1' union all
select 3, 'user2'

insert @t2
select 1 union all select 3

-- Actual query
select id from @t1 t1 left join @t2 t2 on t1.id = t2.fkid
where t2.fkid is null[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Dallr
Yak Posting Veteran

87 Posts

Posted - 2007-10-11 : 11:07:43

You could use a subquery in the WHERE clause but using a LEFT JOIN will be faster.
Try the following.


SELECT T1.ID, T1.UserID
FROM Table1 T1 
	LEFT JOIN Table2 T2 ON T1.ID = T2.FkID
WHERE T2.fkID IS NULL AND T1.userid = 'user1'

Dallr

jpogorman
Starting Member

2 Posts

Posted - 2007-10-11 : 11:37:47

Thats exactly what I was looking for. Thank you both very much. I would be a while coming up with that myself.
JP

Dallr
Yak Posting Veteran

87 Posts

Posted - 2007-10-11 : 13:31:59

No problem Harsh and I were happy to assist!

Dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources