Select query

[bikerbabe]

Hi,
I have a database table symptill with two fields:
illness_id, symptom_id
It is a many to many relationship
e.g
illness_id symptom_id
1 3
1 4
1 7
2 1
2 4
2 3
3 1
etc.
How do I select a list of illness_id's that have several symptom_id's the same?
I have tried
Select * from symptill where symptom_id = 4 and symptom_id = 3;

But all I get is an empty set
Any suggestions?
Thanks
b

[Claire]

select illness_id from table1 where sympton_id = 4 and illness_id in (
select illness_id from table1 where sympton_id = 3 )


SELECT ILLNESS_ID FROM TABLE1 WHERE SYMPTON_ID = 7 AND ILLNESS_ID IN (
select illness_id from table1 where sympton_id = 4 and illness_id in (
select illness_id from table1 where sympton_id = 3 ))

[bikerbabe]

Hi,
I tried that but it gives me a sql syntax error.
I didn't think you could do a subselect in Mysql?
Thanks
b

[jkoopmann]

bikerbabe,
the problem with your first query is that symptom_id can not be 3 AND 4 at the same time. if you changed the 'AND' to 'OR' you will get something meaningful.

on the other hand if you do not know what the symptom_id's are that are accross the illness_id(s) you might try something as simple as the following sql. it will tell you which symptom_id that cause multiple illnesses.
select symptom_id,count(*) from symptill group by symptom_id having count(*) > 1

if you want to see the illnesses and symptoms as a result set then you could try something like :
select illness_id, symptom_id from symptill
where symptom_id in (select symptom_id from symptill group by symptom_id having count(*) > 1)

[bikerbabe]

Thanks for that James
Really appreciate it
b