Обсуждение: SQL Question
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
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 havetable 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)