Message-ID: <419E4A4C.9010307@activestateway.com>
From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
Reply-To: ischamay.andbergsay@activestateway.com
User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040803)
MIME-Version: 1.0
Subject: Re: SQL Question
References: <cnk1t6$2c2g$1@news.hub.org>
In-Reply-To: <cnk1t6$2c2g$1@news.hub.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 32
Date: Fri, 19 Nov 2004 19:23:43 GMT
To: pgsql-sql@postgresql.org

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)