CODEWITHSUNDEEP
mysqlsqljoin
Bram'in
Bram'in

Reputation: 628

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

Upvotes: 0

Views: 1322

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

The WHERE clause selects only materials 8 and 10. The GROUP BY then groups these by p.name. The question is: Does a single name have both these materials? Well, that is what count(distinct) does.

Upvotes: 1

Related Questions