Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GL00V08686 for ; Wed, 16 Jan 2002 16:00:06 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 834 invoked from network); 16 Jan 2002 17:09:30 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 17:09:30 -0000 Message-ID: <031001c19ed1$754da1e0$4d00a8c0@catamount> From: "Glenn MacGregor" To: "Glenn MacGregor" , References: <02bd01c19ec6$16e11ed0$4d00a8c0@catamount> Subject: Re: Complex view question Date: Wed, 16 Jan 2002 16:04:59 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_030D_01C19EA7.8C730600" 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: 200201/193 X-Sequence-Number: 6279 This is a multi-part message in MIME format. ------=_NextPart_000_030D_01C19EA7.8C730600 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have simplified this by creating some views that do work, but I still hav= ing the same problem. So what is the difference between the two queries: Query 1: returns correct number of rows select * from overperms where username=3D'test' union select * from default= perms where username=3D'test' and vimname not in (select vimname from overp= erms where username=3D'test'); Query 2: return incorrect number of rows create view perms as select * from overperms union select * from defaultper= ms where vimname not in (select vimname from overperms); select * from perms where username=3D'test'; Thanks Glenn ----- Original Message -----=20 From: Glenn MacGregor=20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, January 16, 2002 2:43 PM Subject: [SQL] Complex view question Hi All...again, I have this complex query with a union and a not in subselect. Query: select xpressvim.vimname, xpressvimpermission.commandaccess, xpressvimper= mission.eventaccess from xpressvim left join xpressvimpermission using (vim= id) left join xpressgroup using (groupid) left join xpressuser using (group= id) where (xpressvimpermission.commandaccess is not null and xpressuser.use= rname =3D 'gtm@jabber.oracom.com') union select xpressvim.vimname, xpressgr= oup.commandaccess, xpressgroup.eventaccess from xpressgroup left join xpres= suser using (groupid) natural join xpressvim where xpressuser.username =3D = 'gtm@jabber.oracom.com' and xpressgroup.commandaccess !=3D 'n' and xpressvi= m.vimname not in (select xpressvim.vimname from xpressvim left join xpressv= impermission using (vimid) left join xpressgroup using (groupid) left join = xpressuser using (groupid) where (xpressvimpermission.commandaccess is not = null and xpressuser.username =3D 'gtm@jabber.oracom.com')); I want to make that a view where I can change the username =3D '' to what= ever user I need it to be. So I do the following: create view tmpview as select xpressvim.vimname, xpressvimpermission.comm= andaccess, xpressvimpermission.eventaccess, xpressuser.username from xpress= vim left join xpressvimpermission using (vimid) left join xpressgroup using= (groupid) left join xpressuser using (groupid) union select xpressvim.vimn= ame, xpressgroup.commandaccess, xpressgroup.eventaccess, xpressuser.usernam= e from xpressgroup left join xpressuser using (groupid) natural join xpress= vim where xpressvim.vimname not in (select xpressvim.vimname from xpressvim= left join xpressvimpermission using (vimid) left join xpressgroup using (g= roupid) left join xpressuser using (groupid)); remove all the where's except for the not in. If I now select from the v= iew: select * from tmpview where mailto:username=3D'gtm@jabber.oracom.com'= I get results but it is not the same number of rows as the other one. Th= e first one has the correct number of rows, the view doesn't. Did I miss s= omething here? Thanks Glenn ------=_NextPart_000_030D_01C19EA7.8C730600 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have simplified this by creating some vi= ews that=20 do work, but I still having the same problem.  So what is the differen= ce=20 between the two queries:
 
Query 1:  returns correct number of= =20 rows
select * from overperms where username=3D'= test' union=20 select * from defaultperms where username=3D'test' and vimname not in (sele= ct=20 vimname from overperms where username=3D'test');
 
Query 2:  return incorrect number of= =20 rows
create view perms as select * from overper= ms union=20 select * from defaultperms where vimname not in (select vimname from=20 overperms);
 
select * from perms where=20 username=3D'test';
 
    Thanks
 
       =20     Glenn
 
----- Original Message -----
Fro= m:=20 Glenn MacGregor<= /A>=20
Sent: Wednesday, January 16, 2002 = 2:43=20 PM
Subject: [SQL] Complex view=20 question

Hi All...again,
 
I have this complex query with a union a= nd a not=20 in subselect.
 
Query:

select xpressvim.vimname, xpressvimpermission.commandaccess,=20 xpressvimpermission.eventaccess from xpressvim left join xpressvimpermiss= ion=20 using (vimid) left join xpressgroup using (groupid) left join xpressuser = using=20 (groupid) where (xpressvimpermission.commandaccess is not null and=20 xpressuser.username =3D 'gtm@jabber.oracom.com') union select xpressvim.v= imname,=20 xpressgroup.commandaccess, xpressgroup.eventaccess from xpressgroup left = join=20 xpressuser using (groupid) natural join xpressvim where xpressuser.userna= me =3D=20 'gtm@jabber.oracom.com' and xpressgroup.commandaccess !=3D 'n' and=20 xpressvim.vimname not in (select xpressvim.vimname from xpressvim left jo= in=20 xpressvimpermission using (vimid) left join xpressgroup using (groupid) l= eft=20 join xpressuser using (groupid) where (xpressvimpermission.commandaccess = is=20 not null and xpressuser.username =3D 'gtm@jabber.oracom.com'));

 

I want to make that a view where I can change the username =3D '' to w= hatever=20 user I need it to be.  So I do the following:

create view tmpview as select xpressvim.vimname,=20 xpressvimpermission.commandaccess, xpressvimpermission.eventaccess,=20 xpressuser.username from xpressvim left join xpressvimpermission using (v= imid)=20 left join xpressgroup using (groupid) left join xpressuser using (groupid= )=20 union select xpressvim.vimname, xpressgroup.commandaccess,=20 xpressgroup.eventaccess, xpressuser.username from xpressgroup left join= =20 xpressuser using (groupid) natural join xpressvim where xpressvim.vimname= not=20 in (select xpressvim.vimname from xpressvim left join xpressvimpermission= =20 using (vimid) left join xpressgroup using (groupid) left join xpressuser = using=20 (groupid));

remove all the where's except for the not in.  If I now select fr= om=20 the view:  select * from tmpview where mailto:username=3D'gtm= @jabber.oracom.com' =20 I get results but it is not the same number of rows as the other one.&nbs= p;=20 The first one has the correct number of rows, the view doesn't.  Did= I=20 miss something here?

 

Thanks

       =20 Glenn

------=_NextPart_000_030D_01C19EA7.8C730600--