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 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)