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