Igor Kryltsov wrote:
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)??
Okay: suppose you have table my_facilities(facility_id integer) --- your facilityN1...facilityN100
SELECT group_id FROM facility_group s1 JOIN my_facilities s2 USING(facility_id) GROUP BY group_id HAVING COUNT(*) = (SELECT COUNT(*) FROM my_facilities)
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
