Hi,
If anybody can offer better SQL query please.
We have table
create table group_facility (
group_id integer not null,
facility_id integer not null
)
It stores facilities membership in group. For example: "North Region" -
facilityA, facilityB
I need to extract groups from this table which contain facilityN AND
facilityZ and may be others but these two(both) has to be a group member.
Query:
SELECT DISTINCT group_id FROM facility_group s1
WHERE EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id =
s1.group_id AND facility_id = 390)
AND
EXISTS (SELECT 1 FROM facility_group s2 WHERE s2.group_id = s1.group_id AND
facility_id = 999)
works but what if I need to find groups where membership is (facilityN1,
....facilityN100)??
Thank you,
Igor K