From gtm@oracom.com Tue Nov 27 22:19:16 2001 Received: from degroens.oracom.com ([63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id fARMJGm20877 for ; Tue, 27 Nov 2001 17:19:16 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 17509 invoked from network); 27 Nov 2001 18:52:58 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 27 Nov 2001 18:52:58 -0000 Message-ID: <020c01c17792$2176f820$4d00a8c0@catamount> From: "Glenn MacGregor" To: Subject: Complex query help please Date: Tue, 27 Nov 2001 17:23:25 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0209_01C17768.389641C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200111/371 X-Sequence-Number: 5753 This is a multi-part message in MIME format. ------=_NextPart_000_0209_01C17768.389641C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I am new to sql and have the need for a complex query. I have figured out = how to do it in two queries but I would like to get it down to one. I will= give you my queries and hopefully some can give me some assistance. Thanks Query 1: create temp table ttable1 as select xpressuser.username, xpressvimpermissio= n.commandaccess, xpressvimpermission.eventaccess from xpressvim left join x= pressvimpermission using (vimid) left join xpressgroup using (groupid) left= join xpressuser using (groupid) where xpressvim.vimname =3D 'testvim2'; creates a temp table. Query 2: select * from ttable1 union select xpressuser.username, xpressgroup.command= access, xpressgroup.eventaccess from xpressgroup left join xpressuser usin= g (groupid) where xpressuser.username not in (select username from ttable1)= and (xpressgroup.commandaccess !=3D 'n' or xpressgroup.eventaccess !=3D '= f'); So the idea is I have a default set of permissions that users get, these pe= rmissions can be overridden on each vim for each group(user). The first qu= ery results in a table which has all the overridden permissions. The second query results in a table which has all the default permissions u= nioned with the previous table and not in the previous table. Is there any way to do this one query or a function? Thanks Glenn ------=_NextPart_000_0209_01C17768.389641C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 
I am new to sql and have the need for a co= mplex=20 query.  I have figured out how to do it in two queries but I would lik= e to=20 get it down to one.  I will give you my queries and hopefully some can= give=20 me some assistance.
Thanks
 
Query 1:
create temp table ttable1 as select=20 xpressuser.username, xpressvimpermission.commandaccess,=20 xpressvimpermission.eventaccess from xpressvim left join xpressvimpermissio= n=20 using (vimid) left join xpressgroup using (groupid) left join xpressuser us= ing=20 (groupid) where xpressvim.vimname =3D 'testvim2';
 
creates a temp table.
 
Query 2:
select * from ttable1 union select=20 xpressuser.username, xpressgroup.commandaccess,  xpressgroup.eventacce= ss=20 from xpressgroup left join xpressuser using (groupid) where xpressuser.user= name=20 not in (select username from ttable1) and (xpressgroup.commandaccess !=3D '= n'=20 or  xpressgroup.eventaccess !=3D 'f');
 
So the idea is I have a default set of per= missions=20 that users get, these permissions can be overridden on each vim for each=20 group(user).  The first query results in a table which has all the=20 overridden permissions.
The second query results in a table which = has all=20 the default permissions unioned with the previous table and not in the prev= ious=20 table.
 
Is there any way to do this one query or a= =20 function?
 
    Thanks
 
       =20     Glenn
------=_NextPart_000_0209_01C17768.389641C0-- From josh@agliodbs.com Wed Nov 28 00:24:37 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAS0Obm24856 for ; Tue, 27 Nov 2001 19:24:37 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.7) with HTTP id 514821; Tue, 27 Nov 2001 16:24:38 -0800 From: "Josh Berkus" Subject: Re: Complex query help please To: "Glenn MacGregor" Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.7 Date: Tue, 27 Nov 2001 16:24:38 -0800 Message-ID: In-Reply-To: <020c01c17792$2176f820$4d00a8c0@catamount> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200111/374 X-Sequence-Number: 5756 Glenn, > I am new to sql and have the need for a complex query. I have > figured out how to do it in two queries but I would like to get it > down to one. I will give you my queries and hopefully some can give > me some assistance. > Thanks Easy. Take your second query, and everywhere you have "ttable1" replace it with a sub-select which is the first query. Thus: Query 1: create temp table ttable1 as select xpressuser.username, xpressvimpermission.commandaccess, xpressvimpermission.eventaccess from xpressvim left join xpressvimpermission using (vimid) left join xpressgroup using (groupid) left join xpressuser using (groupid) where xpressvim.vimname = 'testvim2'; creates a temp table. select xpressuser.username, xpressvimpermission.commandaccess, xpressvimpermission.eventaccess from xpressvim left join xpressvimpermission using (vimid) left join xpressgroup using (groupid) left join xpressuser using (groupid) where xpressvim.vimname = 'testvim2' union select xpressuser.username, xpressgroup.commandaccess, xpressgroup.eventaccess from xpressgroup left join xpressuser using (groupid) where xpressuser.username not in (select xpressuser.username from xpressvim left join xpressvimpermission using (vimid) left join xpressgroup using (groupid) left join xpressuser using (groupid) where xpressvim.vimname = 'testvim2') and (xpressgroup.commandaccess != 'n' or xpressgroup.eventaccess != 'f'); You can play with different query structures (WHERE NOT EXISTS, etc) for performance reasons. Certainly you should save the above as a view to get optimization. ALso, make sure that you're using Postgres 7.1.0 or above. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco