SQL query that meets a parameter that is a set

There are a few ways to do this. One that I like a lot uses GROUP BY and the prior knowledge of how many symptoms you need to marry.

An example, assuming the actual structure of your database:

SELECT
    Doenca.*

FROM CONSULTA_SINTOMA cs

    INNER JOIN DOENCA_SINTOMA ds
    ON ds.sintoma_id = cs.sintoma_id
    INNER JOIN DOENCA Doenca
    ON Doenca.id = ds.doenca_id

WHERE cs.consulta_id = 1  -- a consulta
AND cs.sintoma_id IN (1,2,3) -- os sintomas

GROUP BY Doenca.id
HAVING COUNT(*) = 3 -- porque são 3 sintomas

A shorter variation, which does not require explicitly passing the symptoms, but still requires knowing how many are:

SELECT
    Doenca.*
FROM DOENCA_SINTOMA ds
    INNER JOIN DOENCA Doenca
    ON Doenca.id = ds.doenca_id
WHERE ds.sintoma_id IN (
   SELECT sintoma_id FROM CONSULTA_SINTOMA
   WHERE consulta_id = 1 -- a consulta
) -- os sintomas

GROUP BY Doenca.id
HAVING COUNT(*) = 3 -- porque são 3 sintomas

I do not know how your columns are, but I've tried to deduce that the keys are ID_TABLE.

The disease has at least one symptom:

  SELECT CO.ID_CONSULTA, SI.ID_SINTOMA, DO.ID_DOENCA FROM CONSULTA CO
    INNER JOIN CONSULTA_SINTOMA CS ON CO.ID_CONSULTA = CS.ID_CONSULTA
    INNER JOIN SINTOMA SI ON CS.ID_SINTOMA = SI.ID_SINTOMA
    INNER JOIN DOENCA_SINTOMA DS ON SI.ID_SINTOMA = DS.ID_SINTOMA
    INNER JOIN DOENCA DO ON DS.ID_DOENCA = DO.ID_DOENCA

For each disease will repeat the symptom and query, you just need to change the fields you want to bring in SELECT .

The disease has all the symptoms: (Using the ALL operator)

  SELECT * FROM DOENCA DO
    INNER JOIN DOENCA_SINTOMA DS ON DO.ID_DOENCA = DS.ID_DOENCA
    WHERE DS.ID_SINTOMA = ALL (SELECT CS.ID_SINTOMA 
                               FROM CONSULTA_SINTOMA CS
                               WHERE CS.ID_CONSULTA = x)

x will be your query code

• Query made in MySQL 5.5.32

First you should create a table of diseases and another of symptoms and then one of relationship between diseases and symptoms. For queries you should do something similar, one for queries and one for relationship symptoms-queries.

It's easy to find out what diseases present the n symptoms.

Note: I hope this is a "rhetorical" question and not for the purpose of creating a diagnostic system.

I have not tested. Quick answer so you can test:

SELECT SI.doencaId FROM sintomas SI    
WHERE SI.sintomasId IN (
  SELECT CO_SI.sintomasID FROM consultas_sintomas CO_SI 
  WHERE CO_SI.consultaId = id
)  

id - id of the query for which you want to know possible diseases