Selecting what's in one table and not in another

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)

Selecting what's in one table and not in another

Author

Topic

outspaced
Starting Member

21 Posts

Posted - 2007-01-24 : 06:37:15

Hi,

I have 2 tables, both with an id_number field. I want to select everything from tbl 1 that does not have an id_number that is also present in tbl 2 (in the same field). None of the things I thought would do it give me correct results. Help!

Thanks,
Alex

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 06:52:00

select t1.* from table1 as t1
left join table2 as t2 on t2.id_number = t1.id_number
where t2.id_number is null

Peter Larsson
Helsingborg, Sweden

outspaced
Starting Member

21 Posts

Posted - 2007-01-25 : 07:09:40

Great stuff, thanks.

Now I want to select the same rows, except I don't want ones in t2 where the field 'testing' is NULL. Any ideas?

Everything I try seems to return the wrong records :o/

Thanks,
Alex

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 07:11:58

select t1.* from table1 as t1
left join table2 as t2 on t2.id_number = t1.id_number
where t2.id_number is null and t2.Testing IS NOT NULL

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-25 : 07:15:58

"Now I want to select the same rows, except I don't want ones in t2 where the field 'testing' is NULL."

How difficult is it to convert that simple english statement into equivalent SQL condition?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

outspaced
Starting Member

21 Posts

Posted - 2007-01-25 : 07:27:36

I already tried this - it doesn't return me any rows.

if t2.id_number is NULL, then surely it isn't selecting anything that is present in that table? id_number is the primary key.

't2.id_number is NULL and t2.id_number is not null' could never happen.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-25 : 07:45:24

[code]Select * from table1 as t1
where not exists(select * from table2 as t2 on t1.id_number = t2.id_number and t2.testing is NULL)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-25 : 09:37:14

[code]select t1.*
from table1 as t1
left join (
select id_number
from table2
where testing is not null
) as t2 on t2.id_number = t1.id_number
where t2.id_number is null[/code]or[code]select t1.*
from table1 as t1
left join table2 as t2 on t2.id_number = t1.id_number and t2.testing is not null
where t2.id_number is null [/code]

Peter Larsson
Helsingborg, Sweden

outspaced
Starting Member

21 Posts

Posted - 2007-01-25 : 10:34:00

That will do nicely! Thanks a lot for the help.

cheers,
Alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources