SQL – Multiple many-to-many relations filtering SELECT

Programming in sql: SQL – Multiple many-to-many relations filtering SELECT on newest questions tagged sql – Stack Overflow

These are my tables:

Cadastros (id, nome)
Convenios (id, nome)
Especialidades (id, nome)
Facilidades (id, nome)

And the join tables:

cadastros_convenios
cadastros_especialidades
cadastros_facilidades

The table I’m querying for: Cadastros

I’m using MySQL.

The system will allow the user to select multiple “Convenios”, “Especialidades” and “Facilidades”. Think of each of these tables as a different type of “tag”. The user will be able to select multiple “tags” of each type.

What I want is to select only the results in Cadastros table that are related with ALL the “tags” from the 3 different tables provided. Please note it’s not an “OR” relation. It should only return the row from Cadastros if it has a matching link table row for EVERY “tag” provided.

Here is what I have so far:

SELECT Cadastro.*, Convenio.* FROM Cadastros AS Cadastro
    INNER JOIN cadastros_convenios AS CadastrosConvenio ON(Cadastro.id = CadastrosConvenio.cadastro_id)
INNER JOIN Convenios AS Convenio ON (CadastrosConvenio.convenio_id = Convenio.id AND Convenio.id IN(2,3))
    INNER JOIN cadastros_especialidades AS CadastrosEspecialidade ON (Cadastro.id = CadastrosEspecialidade.cadastro_id)
INNER JOIN Especialidades AS Especialidade ON(CadastrosEspecialidade.especialidade_id = Especialidade.id AND Especialidade.id IN(1))
    INNER JOIN cadastros_facilidades AS CadastrosFacilidade ON (Cadastro.id = CadastrosFacilidade.cadastro_id)
INNER JOIN Facilidades AS Facilidade ON(CadastrosFacilidade.facilidade_id = Facilidade.id AND Facilidade.id IN(1,2))
GROUP BY Cadastro.id
HAVING COUNT(*) = 5;

I’m using the HAVING clause to try to filter the results based on the number of times it shows (meaning the number of times it has been successfully “INNER JOINED”). So in every case, the count should be equal to the number of different filters I added. So if I add 3 different “tags”, the count should be 3. If I add 5 different tags, the count should be 5 and so on. It works fine for a single relation (a single pair of inner joins). When I add the other 2 relations it starts to lose control.

EDIT

Here is something that I believe is working (thanks @Tomalak for pointing out the solution with sub-queries):

    SELECT Cadastro.*, Convenio.*, Especialidade.*, Facilidade.* FROM Cadastros AS Cadastro

    INNER JOIN cadastros_convenios AS CadastrosConvenio ON(Cadastro.id = CadastrosConvenio.cadastro_id)
INNER JOIN Convenios AS Convenio ON (CadastrosConvenio.convenio_id = Convenio.id)

    INNER JOIN cadastros_especialidades AS CadastrosEspecialidade ON (Cadastro.id = CadastrosEspecialidade.cadastro_id)
INNER JOIN Especialidades AS Especialidade ON(CadastrosEspecialidade.especialidade_id = Especialidade.id)

    INNER JOIN cadastros_facilidades AS CadastrosFacilidade ON (Cadastro.id = CadastrosFacilidade.cadastro_id)
INNER JOIN Facilidades AS Facilidade ON(CadastrosFacilidade.facilidade_id = Facilidade.id)

WHERE
(SELECT COUNT(*) FROM cadastros_convenios WHERE cadastro_id = Cadastro.id AND convenio_id IN(1, 2, 3)) = 3
AND
(SELECT COUNT(*) FROM cadastros_especialidades WHERE cadastro_id = Cadastro.id AND especialidade_id IN(3)) = 1
AND
(SELECT COUNT(*) FROM cadastros_facilidades WHERE cadastro_id = Cadastro.id AND facilidade_id IN(2, 3)) = 2

GROUP BY Cadastro.id

But I’m concerned about performance. It looks like these 3 sub-queries in the WHERE clause are gonna be over-executed…

Another solution

It joins subsequent tables only if the previous joins were a success (if no rows match one of the joins, the next joins are gonna be joining an empty result-set) (thanks @DRapp for this one)

SELECT STRAIGHT_JOIN
  Cadastro.*
   FROM
  ( SELECT Qualify1.cadastro_id
       from
             ( SELECT cc1.cadastro_id
                  FROM cadastros_convenios cc1
                  WHERE cc1.convenio_id IN (1, 2, 3)
                  GROUP by cc1.cadastro_id
                  having COUNT(*) = 3 ) Qualify1

             JOIN
             ( SELECT ce1.cadastro_id
                  FROM cadastros_especialidades ce1
                  WHERE ce1.especialidade_id IN( 3 )
                  GROUP by ce1.cadastro_id
                  having COUNT(*) = 1 ) Qualify2
                ON (Qualify1.cadastro_id = Qualify2.cadastro_id)

                  JOIN
                  ( SELECT cf1.cadastro_id
                       FROM cadastros_facilidades cf1
                       WHERE cf1.facilidade_id IN (2, 3)
                       GROUP BY cf1.cadastro_id
                       having COUNT(*) = 2 ) Qualify3
                  ON (Qualify2.cadastro_id = Qualify3.cadastro_id) ) FullSet
  JOIN Cadastros AS Cadastro
     ON FullSet.cadastro_id = Cadastro.id

     INNER JOIN cadastros_convenios AS CC
        ON (Cadastro.id = CC.cadastro_id)
        INNER JOIN Convenios AS Convenio
           ON (CC.convenio_id = Convenio.id)

     INNER JOIN cadastros_especialidades AS CE
        ON (Cadastro.id = CE.cadastro_id)
        INNER JOIN Especialidades AS Especialidade
           ON (CE.especialidade_id = Especialidade.id)

     INNER JOIN cadastros_facilidades AS CF
        ON (Cadastro.id = CF.cadastro_id)
        INNER JOIN Facilidades AS Facilidade
           ON (CF.facilidade_id = Facilidade.id)
GROUP BY Cadastro.id

source: http://stackoverflow.com/questions/9849510/sql-multiple-many-to-many-relations-filtering-select
Programming in sql: programming-in-sql