X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6B35A3A49D6 for ; Fri, 19 Nov 2004 06:12:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 79619-06 for ; Fri, 19 Nov 2004 06:12:29 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 4AD6C3A49D5 for ; Fri, 19 Nov 2004 06:12:29 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id iAJ6CSsp081201 for ; Fri, 19 Nov 2004 06:12:28 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id iAJ5tpld077907 for pgsql-sql@postgresql.org; Fri, 19 Nov 2004 05:55:51 GMT From: "Igor Kryltsov" X-Newsgroups: comp.databases.postgresql.sql Subject: SQL Question Date: Fri, 19 Nov 2004 16:57:01 +1100 Organization: Hub.Org Networking Services Lines: 31 Message-ID: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.3 tagged_above=0.0 required=5.0 tests=FORGED_YAHOO_RCVD, PRIORITY_NO_NAME X-Spam-Level: * X-Archive-Number: 200411/223 X-Sequence-Number: 19542 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