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