From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Question
Date: Fri, 19 Nov 2004 19:23:43 GMT
Message-ID: <419E4A4C.9010307@activestateway.com> (raw)
In-Reply-To: <cnk1t6$2c2g$1@news.hub.org>
References: <cnk1t6$2c2g$1@news.hub.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ischamay.andbergsay@activestateway.com
Subject: Re: SQL Question
In-Reply-To: <419E4A4C.9010307@activestateway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox