From gtm@oracom.com Wed Jan 16 19:38:37 2002 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GJcbV92676 for ; Wed, 16 Jan 2002 14:38:37 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 30890 invoked from network); 16 Jan 2002 15:49:32 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 15:49:32 -0000 Message-ID: <02bd01c19ec6$16e11ed0$4d00a8c0@catamount> From: "Glenn MacGregor" To: Subject: Complex view question Date: Wed, 16 Jan 2002 14:43:36 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02BA_01C19E9C.2E0375B0" 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/192 X-Sequence-Number: 6278 This is a multi-part message in MIME format. ------=_NextPart_000_02BA_01C19E9C.2E0375B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All...again, I have this complex query with a union and a not in subselect. Query: select xpressvim.vimname, xpressvimpermission.commandaccess, xpressvimpermi= ssion.eventaccess from xpressvim left join xpressvimpermission using (vimid= ) left join xpressgroup using (groupid) left join xpressuser using (groupid= ) where (xpressvimpermission.commandaccess is not null and xpressuser.usern= ame =3D 'gtm@jabber.oracom.com') union select xpressvim.vimname, xpressgrou= p.commandaccess, xpressgroup.eventaccess from xpressgroup left join xpressu= ser using (groupid) natural join xpressvim where xpressuser.username =3D 'g= tm@jabber.oracom.com' and xpressgroup.commandaccess !=3D 'n' and xpressvim.= vimname not in (select xpressvim.vimname from xpressvim left join xpressvim= permission using (vimid) left join xpressgroup using (groupid) left join xp= ressuser using (groupid) where (xpressvimpermission.commandaccess is not nu= ll and xpressuser.username =3D 'gtm@jabber.oracom.com')); I want to make that a view where I can change the username =3D '' to whatev= er user I need it to be. So I do the following: create view tmpview as select xpressvim.vimname, xpressvimpermission.comman= daccess, xpressvimpermission.eventaccess, xpressuser.username from xpressvi= m left join xpressvimpermission using (vimid) left join xpressgroup using (= groupid) left join xpressuser using (groupid) union select xpressvim.vimnam= e, xpressgroup.commandaccess, xpressgroup.eventaccess, xpressuser.username = from xpressgroup left join xpressuser using (groupid) natural join xpressvi= m where xpressvim.vimname not in (select xpressvim.vimname from xpressvim l= eft join xpressvimpermission using (vimid) left join xpressgroup using (gro= upid) left join xpressuser using (groupid)); remove all the where's except for the not in. If I now select from the vie= w: 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. The = first one has the correct number of rows, the view doesn't. Did I miss som= ething here? Thanks Glenn ------=_NextPart_000_02BA_01C19E9C.2E0375B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All...again,
 
I have this complex query with a union and= a not in=20 subselect.
 
Query:

select xpressvim.vimname, 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 (xpressvimpermission.commandaccess is not null and=20 xpressuser.username =3D 'gtm@jabber.oracom.com') union select xpressvim.vim= name,=20 xpressgroup.commandaccess, xpressgroup.eventaccess from xpressgroup left jo= in=20 xpressuser using (groupid) natural join xpressvim where xpressuser.username= =3D=20 'gtm@jabber.oracom.com' and xpressgroup.commandaccess !=3D 'n' and=20 xpressvim.vimname not in (select xpressvim.vimname from xpressvim left join= =20 xpressvimpermission using (vimid) left join xpressgroup using (groupid) lef= t=20 join xpressuser using (groupid) where (xpressvimpermission.commandaccess is= not=20 null and xpressuser.username =3D 'gtm@jabber.oracom.com'));

 

I want to make that a view where I can change the username =3D '' to wha= tever=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 (vim= id)=20 left join xpressgroup using (groupid) left join xpressuser using (groupid) = union=20 select xpressvim.vimname, xpressgroup.commandaccess, xpressgroup.eventacces= s,=20 xpressuser.username from xpressgroup left join xpressuser using (groupid)= =20 natural join xpressvim where xpressvim.vimname not in (select xpressvim.vim= name=20 from xpressvim left join xpressvimpermission using (vimid) left join xpress= group=20 using (groupid) left join xpressuser using (groupid));

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

 

Thanks

       =20 Glenn

------=_NextPart_000_02BA_01C19E9C.2E0375B0-- From gtm@oracom.com Wed Jan 16 21:00:06 2002 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-- From josh@agliodbs.com Wed Jan 16 21:27:48 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0GLRmV17945 for ; Wed, 16 Jan 2002 16:27:48 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 621524; Wed, 16 Jan 2002 13:27:48 -0800 From: "Josh Berkus" Subject: Re: Complex view question To: "Glenn MacGregor" , "Glenn MacGregor" , X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Wed, 16 Jan 2002 13:27:48 -0800 Message-ID: In-Reply-To: <031001c19ed1$754da1e0$4d00a8c0@catamount> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200201/194 X-Sequence-Number: 6280 Glenn, First, I think that you would find your own queries easier to debug if you used some indenting and line breaks to organize the text. Whenever I run into a query problem, that's what I do and frequently the missed clause or problem aggregate becomes obvious. > Query 1: returns correct number of rows > select * from overperms where username='test' union select * from > defaultperms where username='test' and vimname not in (select vimname from > overperms where username='test'); > > Query 2: return incorrect number of rows > create view perms as select * from overperms union select * from defaultperms > where vimname not in (select vimname from overperms); > > select * from perms where username='test'; These two are NOT the same query, so it's unsurprising that the counts come out different. In the second query, you are excluding ALL rows present in overperms from the count of defaultperms, not just those rows with a username of 'test'. Thus, if vimname <-> username parings are variable, you will indeed get different counts for the first query than the second. Also, I don't think that your query structure is optimal. I think you're making this harder than it needs to be. However, I can't tell without seeing your data structure. -Josh Berkus ______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 From gtm@oracom.com Wed Jan 16 21:33:02 2002 Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GLX2V20921 for ; Wed, 16 Jan 2002 16:33:02 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 1829 invoked from network); 16 Jan 2002 17:41:59 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 17:41:59 -0000 Message-ID: <034a01c19ed6$135c2380$4d00a8c0@catamount> From: "Glenn MacGregor" To: "Josh Berkus" , References: Subject: Re: Complex view question Date: Wed, 16 Jan 2002 16:38:02 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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/195 X-Sequence-Number: 6281 So is there a way to do query 1 with a view? The query is not optimized, I want to get it working first. Thanks Glenn ----- Original Message ----- From: "Josh Berkus" To: "Glenn MacGregor" ; "Glenn MacGregor" ; Sent: Wednesday, January 16, 2002 4:27 PM Subject: Re: [SQL] Complex view question > Glenn, > > First, I think that you would find your own queries easier to debug if you used > some indenting and line breaks to organize the text. Whenever I run into a > query problem, that's what I do and frequently the missed clause or problem > aggregate becomes obvious. > > > Query 1: returns correct number of rows > > select * from overperms where username='test' union select * from > > defaultperms where username='test' and vimname not in (select vimname from > > overperms where username='test'); > > > > Query 2: return incorrect number of rows > > create view perms as select * from overperms union select * from defaultperms > > where vimname not in (select vimname from overperms); > > > > select * from perms where username='test'; > > These two are NOT the same query, so it's unsurprising that the counts come out > different. In the second query, you are excluding ALL rows present in > overperms from the count of defaultperms, not just those rows with a username > of 'test'. Thus, if vimname <-> username parings are variable, you will > indeed get different counts for the first query than the second. > > Also, I don't think that your query structure is optimal. I think you're > making this harder than it needs to be. However, I can't tell without seeing > your data structure. > > -Josh Berkus > > ______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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > From josh@agliodbs.com Wed Jan 16 21:47:49 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0GLlnV26503 for ; Wed, 16 Jan 2002 16:47:49 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 621545; Wed, 16 Jan 2002 13:47:49 -0800 From: "Josh Berkus" Subject: Re: Complex view question To: "Glenn MacGregor" , "Josh Berkus" , X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Wed, 16 Jan 2002 13:47:49 -0800 Message-ID: In-Reply-To: <034a01c19ed6$135c2380$4d00a8c0@catamount> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200201/196 X-Sequence-Number: 6282 Glenn, > So is there a way to do query 1 with a view? > > The query is not optimized, I want to get it working first. Data structure, please. I can't tell what we're working with here. Also, a statement of what you want the result of the view to be would be helpful. Finally, it may not be possible to do exactly what you want with a view, in which case you'll need to use a straight query. -Josh Berkus ______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