G D G D - 5 months ago 6
SQL Question

Return rows only if matches all list values

Let's say I have a table

customers
:

-----------------


|id|name|country|


|1 |Joe |Mexico |


|2 |Mary|USA |


|3 |Jim |France |


-----------------



And a table
languages
:

-------------


|id|language|


|1 |English |


|2 |Spanish |


|3 |French |


-------------



And a table
cust_lang
:

------------------


|id|custId|langId|


|1 |1 |1 |


|2 |1 |2 |


|3 |2 |1 |


|4 |3 |3 |


------------------



Given a list: ["English", "Spanish", "Portugese"]
Using a
WHERE IN
for the list, it will still return customers with ids 1,2 because they match "English" and "Spanish".
However, the results should be 0 rows returned since no customer matches ALL three terms.
I only want the customer ids to return if it matches the cust_lang table.
For instance, Given a list: ["English", "Spanish"]
I would want the results to be customer Id 1, since he alone speaks both languages.

EDIT: @GordonLinoff - That works!!

Now to make it more complex, what's wrong with this additional related query:

Let's assume I also have a table
degrees
:

-----------


|id|degree|


|1 |PHD |


|2 |BA |


|3 |MD |


-----------



A corresponding join table
cust_deg
:

------------------


|id|custId|degId |


|1 |1 |1 |


|2 |1 |2 |


|3 |2 |1 |


|4 |3 |3 |


------------------



The following query does not work. However, it is two of the same queries combined. The results should be only rows that match both lists, instead of the one list.

SELECT * FROM customers C
WHERE C.id IN (
SELECT CL.langId FROM cust_lang CL
JOIN languages L on CL.langId = L.id
WHERE L.language IN ("English", "Spanish")
GROUP BY CL.langID
HAVING COUNT(*) = 2)
AND C.id IN (
SELECT CD.custId FROM cust_deg CD
JOIN degrees D ON CD.degID = D.id
WHERE D.degree IN ("PHD", "BA")
GROUP BY CD.custId HAVING COUNT(*) = 2));`


EDIT2: I think i fixed it. I accidentally had an extra select statement in there.

Answer

You can do this with group by and having:

select cl.custid
from cust_lang cl join
     languages l
     on cl.langid = l.id
where l.language in ('English', 'Spanish', 'Portuguese')
group by cl.custid
having count(*) = 3;

If, for example, you only wanted to check for two languages, then you need only change you WHERE ... IN and HAVING conditions, e.g.:

where l.language in ('English', 'Spanish')

and

having count(*) = 2
Comments