Join tables where ID in one table is *not* present in the other

[Curtis Poe]

I've been thrown to the SQL wolves. I have two tables, 'company' and
'contact', where I need to select information from both, but I only want to
include contacts who do not have IDs in the company table. Below are two
simplified create table commands and appropriate insert statements.

' Begin

CREATE TABLE company (
[companyID] [int] NOT NULL ,
[contactID] [int] NOT NULL ,
[companyName] [varchar] (100) NULL ,
)
GO

CREATE TABLE contact (
[contactID] [int] NOT NULL ,
[companyID] [int] NOT NULL ,
[contactName] [varchar] (100) NULL ,
)
GO

insert into company(companyID, contactID, companyName) values(1,1,'Victorian
Company')
insert into company(companyID, contactID, companyName) values(2,2,'Roman
Company')
GO

insert into contact(contactID, companyID, contactName) values(1,1,'John
Davidson')
insert into contact(contactID, companyID, contactName) values(2,2,'Publius
Ovidius Naso')
insert into contact(contactID, companyID, contactName) values(3,1,'Alfred,
Lord Tennyson')
insert into contact(contactID, companyID, contactName) values(4,2,'Virgil')
GO

' END

It's trivial to find contacts who are also the company contact:

SELECT contact.contactID, contact.companyID,
contact.contactName, company.companyName
FROM contact
INNER JOIN company ON company.contactID = contact.contactID

How do I select the same data for all contacts who do not have a contactID
in the company table? All of my SQL attempts have failed (of course, it
would help if I actually knew SQL :) The tables are actually quite a bit
more involved, but this is a simplified test case.

--
Cheers,
Curtis Poe
Senior Programmer
ONSITE! Technology, Inc.
www.onsitetech.com
503-233-1418

Taking e-Business and Internet Technology To The Extreme!

[Alberto V]

select contacts.*
where NOT EXISTS
(select * from contacts INNER JOIN companies on companies.ID=contacts.ID)

Not so fast, anyway!!

Alberto

"Curtis Poe" <c...@onsitetech.com> ha scritto nel messaggio
news:a2s7ch$d...@dispatch.concentric.net...

[Alberto V]

Or (maybe Faster!!):

Select contacts.*, MyKey=companies.ID into #temp_table
from contacts left outer join companies on contacts.ID=companies.ID

Select * from #temp_table where MyKey is null

Bye
Alberto

[Curtis Poe]

"Alberto V" <vaccariTO...@hotmail.com> wrote in message
news:a2s7l9$nac$1...@newsreader.mailgate.org...

> select contacts.*
> where NOT EXISTS
> (select * from contacts INNER JOIN companies on companies.ID=contacts.ID)
>
> Not so fast, anyway!!
>
> Alberto

Alberto,

Thanks for the SQL. However, after correcting for the table names, it
returns no results:

SELECT contact.*
FROM contact
WHERE (
NOT EXISTS (
SELECT *

FROM contact
INNER JOIN company ON company.contactID = contact.contactID

)
)

[Curtis Poe]

"Alberto V" <vaccariTO...@hotmail.com> wrote in message
news:a2s7l9$nac$1...@newsreader.mailgate.org...

> select contacts.*
> where NOT EXISTS
> (select * from contacts INNER JOIN companies on companies.ID=contacts.ID)
>
> Not so fast, anyway!!
>
> Alberto

Solved the problem with a recursive subselect (I think that's what it's
called). You put me on the right track, though.

SELECT cn1.*
FROM contact cn1

WHERE (
NOT EXISTS (

SELECT cn2.contactID
FROM contact cn2
INNER JOIN company ON company.contactID = cn1.contactID
)
)

[Lars Broberg]

Curtis,

Here is one way to do it (if I understodd you correctly):

SELECT contact.contactID,contact.companyID,contact.contactName
FROM
contact
LEFT OUTER JOIN
company
ON
(company.contactID = contact.contactID)
WHERE
(company.contactID IS NULL)

Hope it helps.
--
Lars Broberg
Elbe-Data AB
Please remove "nothing." from my address...

"Curtis Poe" <c...@onsitetech.com> wrote in message
news:a2s7ch$d...@dispatch.concentric.net...

[Alejandro Izaguirre]

You can do the same in one step, without the temporary table, and you will
get what is the usual idiom for that sort of query:

SELECT CT.contactID, CT.companyID, CT.contactName, CA.companyName
FROM contacts CT
JOIN companies CA ON CA.companyID = CT.companyID
LEFT JOIN companies CB ON CB.contactID = CT.contactID
WHERE CB.contactID IS NULL

/* added the field list and the inner join with CA to get all the fields
required by the OP original query */

Actually, your two step approach is a good illustration to help understand
the meaning of this kind of query, often difficult to grasp for beginners.

Alejandro Izaguirre Martín

"Alberto V" <vaccariTO...@hotmail.com> wrote in message

news:a2s7sj$nde$1...@newsreader.mailgate.org...

[Erland Sommarskog]

[posted and mailed, please reply in news]

Curtis Poe (c...@onsitetech.com) writes:
> Solved the problem with a recursive subselect (I think that's what it's
> called). You put me on the right track, though.
>
> SELECT cn1.*
> FROM contact cn1
> WHERE (
> NOT EXISTS (
> SELECT cn2.contactID
> FROM contact cn2
> INNER JOIN company ON company.contactID = cn1.contactID
> )
> )

Actaully, it's called "correlated subquery".

Furthermore your query can be simplified to:

SELECT cn1.*
FROM contact cn1

WHERE NOT EXISTS (SELECT *
FROM company c
WHERE c.contactID = cn1.contactID)

Not only it saves you typing. More importantly, if the tables are large
it will execute faster.

--
Erland Sommarskog, Abaris AB
som...@algonet.se
SQL Server MVP

[Alberto V]

"Erland Sommarskog" <som...@algonet.se> ha scritto nel messaggio
news:Xns91A32EA5...@127.0.0.1...

>
> Furthermore your query can be simplified to:
>
> SELECT cn1.*
> FROM contact cn1
> WHERE NOT EXISTS (SELECT *
> FROM company c
> WHERE c.contactID = cn1.contactID)
>
> Not only it saves you typing. More importantly, if the tables are large
> it will execute faster.
>

When I first answered, I was a bit in hurry.
That's why my code missed From clause and aliases of table names, and was
surely not optimized...

This code by Erland is surely the best one till now!!

Alberto

[Curtis Poe]

"Lars Broberg" <lar...@elbe-data.nothing.se> wrote in message
news:xOw48.18670$l93.3...@newsb.telia.net...

> Curtis,
>
> Here is one way to do it (if I understodd you correctly):
>
> SELECT contact.contactID,contact.companyID,contact.contactName
> FROM
> contact
> LEFT OUTER JOIN
> company
> ON
> (company.contactID = contact.contactID)
> WHERE
> (company.contactID IS NULL)
>
> Hope it helps.
> --
> Lars Broberg
> Elbe-Data AB
> Please remove "nothing." from my address...

Lars,

Thanks to you and everyone else for the help. I appreciate it. I finally
got off my duff and picked up an SQL book and hopefully, my SQL will be as
good as my Perl, one day :) (just don't look at my .sig as an example of my
Perl code!)

--
Curtis "Ovid" Poe, Senior Programmer, ONSITE! Technology
Someone asked me how to count to 10 in Perl:
push @A, $_ for reverse q.e...q.n.;for(@A){$_=unpack(q|c|,$_);@a=split//;
shift @a;shift @a if $a[$[]eq$[;$_=join q||,@a};print $_,$/for reverse @A