Access SQL using IN where record must satisfy all values

I have a join statement, like this:

SELECT 
distinct(p.id), p.id, p.first_name as [First Name], p.last_name as [Last Name] 
FROM  tbl_person as p , 
tbl_person_languages as pl
WHERE  
pl.person_id = p.id  AND 
pl.language_id in (12,14,...)

but this returns all records. I would like to pull back the distinct record of an individual that has all values of pl.language_id, not just one or more values. I should note, the values in the IN statement may have 1 or more values

Any ideas on how to do this? I’ve tried changing the IN statement to something like this:

 WHERE  
    pl.person_id = p.id  AND 
    pl.language_id =12 AND
    pl.language_id = 14

but this returns nothing.

thanks for any help

Answer

You have to group by and count:

SELECT 
     p.id, p.first_name as [First Name], p.last_name as [Last Name] 
FROM  tbl_person as p , 
tbl_person_languages as pl
WHERE  
pl.person_id = p.id  AND 
pl.language_id in (12,14,...)
GROUP BY p.id, p.first_name, p.last_name
HAVING COUNT(DISTINCT pl.language_id) = 2   -- 12 and 14

Advertisement

Answer