SQL query that meets a parameter that is a set

Question

I have the following situation: in a MEDICAL CONSULTATION I may have one or more SYMPTOMS. Then I have a CONSULTA_SINTOMA table, making the link between CONSULTATION and SYMPTOM. A disease can be identified by one or more symptoms, so I have a DON_SINTOMA table making the link between DISEASE and SYMPTOM.

My problem: I can get in the QUERY table all the ids of symptoms presented in a query. But how can we make sure that we know all the possible diseases? For example: if the symptoms presented in a consultation are: 1, 2 and 3. How to know which diseases are identified by these symptoms?

Answer 1

I don’t know how your columns are, but I tried to deduce that the keys are ID_TABELA.

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 exchange the fields you want to bring in SELECT.

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

  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 consultation code

• Query made in Mysql 5.5.32

Answer 2

There are a few ways to do this. One I quite like uses a GROUP BY and 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 passing the symptoms explicitly, 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

Answer 3

I haven’t tested.
Quick response 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 the possible diseases

Answer 4

First you must create a table of diseases and another of symptoms and then one of relationship between diseases and symptoms. For appointments you should do something pareciso, one of consultations and the other of relationship symptoms-consultations.

Hence it is easy to find out which diseases present the n symptoms.

Note: I hope this is a "rhetorical" question and not in order to create a diagnostic system.