CODEWITHSUNDEEP
sqloracle-databasejoin
Pop Stack
Pop Stack

Reputation: 944

A query to show pairs based on some condition in a join

Write a query to display distinct pair of patients who are treated by the same doctor.
The following tables are present: 

doctor:  
d_id  
d_name  

patient:  
p_id  
p_name  

treatment:  
d_id  
p_id  
disease  
medicine

The query:

Select p1.p_name, p2.p_name
from patient p1, patient p2, treatment t1, treatment t2
where t1.d_id=t2.d_id 
AND t1.p_id<>t2.p_id
AND t1.p_id=p2.p_id
AND t2.p_id=p1.p_id;

Can you suggest a better/alternative query (Oracle style) that eliminates duplicate pairs [like (p1,p6) and (p6,p1)]?

Upvotes: 2

Views: 1468

Answers (2)

Sid
Sid

Reputation: 43

this will retun list of patients trated by same doctor

Select distinct patient.p_name,treatment.p_id,treatment.d_id from treatment inner join patient on treatment.p_id= patient.p_id group by treatment.d_id

Upvotes: 0

Andomar
Andomar

Reputation: 238246

To search for all other patients treated by the same doctor, join the treatments table to itself. You can use distinct to remove duplicates:

SELECT   distinct p1.Name
,        p2.Name
FROM     patient p1
JOIN     treatment t1
ON       p1.id = t1.p_id
JOIN     treatment t2
ON       t1.d_id = t2.d_id -- Same doctor
         and t1.p_id < p2.p_id -- Higher patient nr
JOIN     patient p2
ON       p2.id = t2.p_id

The condition t1.p_id < p2.p_id removes another kind of duplicate, with the patients swapped. John, Mary would be the same pair as Mary, Join. One of thise will be filtered out by the < condition.

Upvotes: 4

Related Questions