create join query on the same table
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
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)
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')
Yes or course... in this case, you would return patients that have an instance of one Facility but not the other.