pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Glenn MacGregor <gtm@oracom.com>
To: Glenn MacGregor <gtm@oracom.com>
To: pgsql-sql@postgresql.org
Subject: Re: Complex view question
Date: Wed, 16 Jan 2002 16:04:59 -0500
Message-ID: <031001c19ed1$754da1e0$4d00a8c0@catamount> (raw)
References: <02bd01c19ec6$16e11ed0$4d00a8c0@catamount>

I have simplified this by creating some views that do work, but I still having the same problem.  So what is the difference between the two queries:

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

    Thanks

            Glenn

  ----- Original Message ----- 
  From: Glenn MacGregor 
  To: pgsql-sql@postgresql.org 
  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, xpressvimpermission.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.username = 'gtm@jabber.oracom.com') union select xpressvim.vimname, xpressgroup.commandaccess, xpressgroup.eventaccess from xpressgroup left join xpressuser using (groupid) natural join xpressvim where xpressuser.username = 'gtm@jabber.oracom.com' and xpressgroup.commandaccess != 'n' and xpressvim.vimname not in (select xpressvim.vimname 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.username = 'gtm@jabber.oracom.com'));



  I want to make that a view where I can change the username = '' to whatever user I need it to be.  So I do the following:

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

  remove all the where's except for the not in.  If I now select from the view:  select * from tmpview where mailto:username='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 something here?



  Thanks

          Glenn

view thread (5+ messages)  latest in thread

Message-ID: <031001c19ed1$754da1e0$4d00a8c0@catamount>
Permalink:  ../031001c19ed1$754da1e0$4d00a8c0@catamount/
Also on:    postgresql.org/message-id/031001c19ed1$754da1e0$4d00a8c0@catamount

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gtm@oracom.com
  Subject: Re: Complex view question
  In-Reply-To: <031001c19ed1$754da1e0$4d00a8c0@catamount>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox