SQL query with multiples values(same column)
-
I have this tables: illness (cod_i,illness) symptoms (cod_s,symptom) and illness_symptoms (cod_i,Cod_s) How can i do to find out a illness that has for example 2/3 symptoms -> cod_i | cod_s i_1 s_1 i_1 s_2 i_1 s_3 that is select the illness that has the s_1 and s_2 symptoms. Please Help me
-
I have this tables: illness (cod_i,illness) symptoms (cod_s,symptom) and illness_symptoms (cod_i,Cod_s) How can i do to find out a illness that has for example 2/3 symptoms -> cod_i | cod_s i_1 s_1 i_1 s_2 i_1 s_3 that is select the illness that has the s_1 and s_2 symptoms. Please Help me
You need to select from the many-to-many table joined to itself, as in (not tested): 1. assuming s_1 and s_2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
WHERE is1.cod_s=s_1 AND is2.cod_s=s_22. Assuming symptom1 and symptom2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
INNER JOIN symptoms AS sy1 ON is1.cod_s=sy1.cod_s
INNER JOIN symptoms AS sy2 ON is2.cod_s=sy2.cod_s
WHERE sy1.symptom=symptom1 AND sy2.symptom=symptom2In both cases, then probably apply one more JOIN to obtain the details of the illnesses found. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
You need to select from the many-to-many table joined to itself, as in (not tested): 1. assuming s_1 and s_2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
WHERE is1.cod_s=s_1 AND is2.cod_s=s_22. Assuming symptom1 and symptom2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
INNER JOIN symptoms AS sy1 ON is1.cod_s=sy1.cod_s
INNER JOIN symptoms AS sy2 ON is2.cod_s=sy2.cod_s
WHERE sy1.symptom=symptom1 AND sy2.symptom=symptom2In both cases, then probably apply one more JOIN to obtain the details of the illnesses found. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
THANKS A LOT MAN i just tried the firs option with 2 and four symptoms and it really works. I doing a program to predict based on four choosen symptoms(combobox) if there are a illness, is like the idea of helping some kind of doctor in the task of diagnose illness. REALLY THANKS first time i ask in this very informative website:java:
Powerful Germany...
-
THANKS A LOT MAN i just tried the firs option with 2 and four symptoms and it really works. I doing a program to predict based on four choosen symptoms(combobox) if there are a illness, is like the idea of helping some kind of doctor in the task of diagnose illness. REALLY THANKS first time i ask in this very informative website:java:
Powerful Germany...
You're welcome. :) PS: you can always vote on a message to express to what extent it has helped you.
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
You need to select from the many-to-many table joined to itself, as in (not tested): 1. assuming s_1 and s_2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
WHERE is1.cod_s=s_1 AND is2.cod_s=s_22. Assuming symptom1 and symptom2 are known:
SELECT * FROM illness_symptoms AS is1
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
INNER JOIN symptoms AS sy1 ON is1.cod_s=sy1.cod_s
INNER JOIN symptoms AS sy2 ON is2.cod_s=sy2.cod_s
WHERE sy1.symptom=symptom1 AND sy2.symptom=symptom2In both cases, then probably apply one more JOIN to obtain the details of the illnesses found. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
my Vote +5 for good example and nice explaination...
Happy Coding... :)