Bram'in Bram'in -4 years ago 115
MySQL Question

SQL Join with multiple conditions for one column

In my situation, I've two tables, a table with all institutions profile and a table which represents material studied by each of these institutions.

In a search, I want to look for institution's name which studied specifics type of material. So it's possible to look for institution which studied "Wood" and "Metal", just "Metal" etc...

I've tried the following query :

SELECT p.name
FROM q1_institution_profiles p
INNER JOIN q9_materials_studied_by_institution pf
ON pf.id_institution = p.id_institution
WHERE pf.id_material_studied = 10 AND pf.id_material_studied = 8


However result is empty because there is a problem when I have multiple conditions (here id_material_studied must be equal 8 and 10).

Is there a way to achieve this properly or do I have to do on join for each criteria ?

Thank you in advance for answer

Answer Source

I think this is what you want:

SELECT p.name
FROM q1_institution_profiles p INNER JOIN
     q9_materials_studied_by_institution pf 
     ON pf.id_institution = p.id_institution
WHERE pf.id_material_studied IN (8, 10)
GROUP BY p.name
HAVING COUNT(DISTINCT pf.id_material_studied) = 2;

That is, get names where there are rows with each of the two values.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download