returning rows w/o match in another 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

General SQL Server Forums

New to SQL Server Programming

returning rows w/o match in another table

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2010-02-09 : 12:31:57

I'm having trouble w/ a large query and think it comes down to a small piece that I might be doing wrong. Here's an example of it...

Table1


userID   username
555        doug
666        steve
777        carl
888        stan

Table2


requestID    userID          
005           555     
006           666

I need to return users that have an entry in Table1, but do NOT have an entry in Table2. How would I do this? Below is how I'm approaching it, but I'm not getting the correct results back.


select    * 
from      Table1 t1
          outer join Table2 t2
            on t1.userID = t2.userID
where    t1.userID <> t2.userID

Thanks in advance for any help!

Sachin.Nand

2937 Posts

Posted - 2010-02-09 : 12:36:04

[code]
select * from Table1 t1

where userid is not null and not exists
(
select 1 from Table2 t2 where t1.userid=t2.userid

)
[/code]

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-09 : 13:34:20

quote:
Originally posted by crugerenator

I need to return users that have an entry in Table1, but do NOT have an entry in Table2. How would I do this? Below is how I'm approaching it, but I'm not getting the correct results back.

select *
from Table1 t1
outer join Table2 t2
on t1.userID = t2.userID
where t1.userID <> t2.userID


select    * 
from      Table1 t1
          LEFT outer join Table2 t2
            on t1.userID = t2.userID
where    t2.userID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources