create join query on the same table

Question

Question

Ahmad Bukhtiar · Nov 29, 2020

#SQL #Other

Hi

I have a table where i want to create join query like

(select * from patients where facility=abc) qry1

left join (select * from patients where facility=xyz) qry2 on qry1.patientid = qry2.patientid

Product version: IRIS 2020.1

Discussion (3)1

Log in or sign up to continue

Vitaly Furman · Nov 30, 2020

Yes or course... in this case, you would return patients that have an instance of one Facility but not the other.

0 0

score 0 · Answer 1 · 2020-11-29T21:09:19-05:00

There might be an easier way:

Select * from HS_Registry.Patient where Facility='ABC' and MPIID in

(select MPIID from HS_Registry.Patient where Facility='XYZ')

MPIID is an indexed field so this should be relatively fast. Since the Patent table is the same you really need to get your "*" data only once; you just need to constrain it to only return a subset of patients that have instances of both Facilities (if I understand you correctly)

score 0 · Answer 2 · 2020-11-30T04:13:41-05:00

thanks can i also use not in like below

Select * from HS_Registry.Patient where Facility='ABC' and MPIID NOT IN

(select MPIID from HS_Registry.Patient where Facility='XYZ')