CODEWITHSUNDEEP
mysqlsqljoin
daytonk
daytonk

Reputation: 27

Not sure which SQL join to use

I have 2 tables (AllClients & AllActivities) and need to retrieve the following information:

I need a list of clients where there are no associated activities.

Here are my tables with some fake data to help explain what I'm looking for:

http://www.givingdirection.com/table.jpg

I basically need the sql to retrieve Sam Johnson because there are no activities for him and he has a TypeCode of 'P'.

Upvotes: 1

Views: 60

Answers (4)

geomagas
geomagas

Reputation: 3280

Since the IN version is missing:

select *
from AllClients c
where TypeCode = 'P'
and LookupCode not in (select distinct LookupCode from AllActivities)

Upvotes: 0

davey
davey

Reputation: 1791

SELECT * 
FROM 
   AllClients 
WHERE 
   NOT EXISTS(SELECT 1 FROM AllActivities WHERE AllActivities.lookUpCode = AllClients.lookUpCode) 
   AND TypeCode = 'P'

Upvotes: 1

Derek
Derek

Reputation: 23318

You don't necessarily need a join

select *
from AllClients c
where TypeCode = 'P'
and not exists (select 1 from AllActivities a where a.LookupCode = c.LookupCode)

Upvotes: 2

juergen d
juergen d

Reputation: 204924

select c.*
from AllClients c 
left join AllActivities a on a.LoopupCode = c.LoopupCode 
where a.LoopupCode is null
and c.TypeCode = 'P'

Upvotes: 1

Related Questions