How do I use JOIN to return a list of tableA's value NOT in tableB's? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I use JOIN to return a list of tableA's value NOT in tableB's?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-14 : 10:29:56

Boon writes "Assuming:


Table A:
number | name
1      | cat 
2      | dog
3      | bird


TableB :
animalID | owner
1        | John
2        | Peter

How do I use JOIN (inner, outer, etc) to return "3 | bird"?, as "3 | bird" is not in tableB

If it is not using JOIN, what method to use then?

*I'm sorry that my question is not clear, as I do not know how to put it in proper words.

Thanks"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-14 : 10:39:39

Select a.number, a.[name] from tablea a where a.number not in (Select b.animalid from tableb b)

Is this what you are after?

Peace

Rick

Edited by - RickD on 06/14/2002 10:40:16

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-14 : 10:41:31

This is good example of relational difference (subtraction). I think a correlated subquery is your best bet...


select
   number,
   name,
from
   tablea a
where
   not exists (
      select 1
      from
          tableb b
      where
          a.number = b.animalid )

You can achieve the same thing with an outer join


select
   a.number,
   a.name,
from
   tablea a
   left outer join tableb b
       on a.number = b.animalid
where
   b.animalid is null

EDIT: damn! sniped . . .

<O>

Edited by - Page47 on 06/14/2002 10:45:07

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-14 : 10:42:27

Hi

Two ways

1. Using a subquery



SELECT * FROm tableA
WHERE number NOT IN (Select animalID FROM TableB)

2. Use a left join. This will return all the records and join
where it can, then we just exclude the ones where the join has
returned rows on the other side



SELECT * 
FROM TableA
LEFT JOIN TableB ON TableB.AnimalID = TableA.number
WHERE
     TableB.animalID IS NULL

Give that a try

Damian

Edited by - merkin on 06/14/2002 10:56:15

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-06-14 : 10:43:26

ROFL!

Double sniped!

I thought about not answering this, all was a little quiet. Then I thought, ahh well, may as well.

Damian

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-14 : 10:45:32

Well, look at it this way, at least he got three different ideas on how to do the same query....

Peace

Rick

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-14 : 10:46:57

Hey Merkin...you need to edit the way you use the code tags cause it screws up formatting.

After you close code tag on a new line hold the alt key and type 0160 and then hit enter. Not sure I truely understand what that does but Fribble told me to do it and it works....

EDIT: Has been fixed (just incase you are looking at this thread and wondering what I am talking about)

<O>

Edited by - Page47 on 06/14/2002 10:57:44

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-14 : 10:48:02

quote:

Well, look at it this way, at least he got three different ideas on how to do the same query....

and I got to say relational difference....

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-14 : 10:52:15

quote:
After you close code tag on a new line hold the alt key and type 0160 and then hit enter. Not sure I truely understand what that does but Fribble told me to do it and it works....

Hmm, maybe I should do a little bit-o-research and find out how to do code tags hey?

Wouldn't normally do something this trivial... but we're kinda lacking questions on the board this morning

-----------------------
Take my advice, I dare ya

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-14 : 10:56:51

quote:

and I got to say relational difference....

You're supposed to read the books, not eat them for breakfast!!

Peace

Rick

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-14 : 11:22:01

quote:

Fribble told me to do it and it works....

It's to work round the bug in IE that manifests because of the way the [code] tags turn out in HTML... I guess the reason it works is that the no-break space character isn't treated as whitespace.

Edited by - Arnold Fribble on 06/14/2002 11:22:59

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-14 : 13:50:32

quote:

quote:

Fribble told me to do it and it works....

It's to work round the bug in IE that manifests because of the way the [code] tags turn out in HTML... I guess the reason it works is that the no-break space character isn't treated as whitespace.

Graz, is this something that can be built into the Snitz translation routine, so that when a closing code tag is found, it will automatically append the character?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources