How to select columns with same set of values in m

Question

My tables are:

patients(pid,name,city)

disease(did,dname)

has_disease(did,pid)

I want to list the patients who have same set of diseases.pid and did are primary keys in patients and disease tables respectively and are foreign keys in has_disease table.

Sample Data:

patients

pid    name   city

1      John    X

2      Jim     Y

3      Jack    Z

disease

 did     dname

  1      Typhoid

  2      Malaria

  3      ViralFever

has_disease

 did     pid
  1       1
  1       2
  3       2
  1       3
  3       3

The answer for the above data is Jim and Jack because they have exactly the same set of diseases 1 and 3 namely malaria and viral fever.I want to know how to implement this in mysql.I tried relational division with where not exists but it doesn't work.

Answer 1

select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
from patients p 
     JOIN has_disease hd ON p.pid=hd.pid
     JOIN disease d ON d.did=hd.did
GROUP BY p.pid;

The query returns us patients and their diseases.

SELECT * 
FROM
    (select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
    from patients p 
         JOIN has_disease hd ON p.pid=hd.pid
         JOIN disease d ON d.did=hd.did
    GROUP BY p.pid) P1
    JOIN
    (select p.*, GROUP_CONCAT(d.did SEPARATOR ', ') AS all_dids
    from patients p 
         JOIN has_disease hd ON p.pid=hd.pid
         JOIN disease d ON d.did=hd.did
    GROUP BY p.pid) P2 ON p1.all_dids=p2.all_dids and p1.pid<>p2.pid

Compare 2 sets of patients by their full list of dids and leave pids with the same dids list but different pids