pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Complex view question
5+ messages / 2 participants
[nested] [flat]

* Complex view question
@ 2002-01-16 19:43 Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:04 ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Glenn MacGregor @ 2002-01-16 19:43 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Complex view question
  2002-01-16 19:43 Complex view question Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 21:04 ` Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:27   ` Re: Complex view question Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Glenn MacGregor @ 2002-01-16 21:04 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Complex view question
  2002-01-16 19:43 Complex view question Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:04 ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 21:27   ` Josh Berkus <josh@agliodbs.com>
  2002-01-16 21:38     ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2002-01-16 21:27 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; Glenn MacGregor <gtm@oracom.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Complex view question
  2002-01-16 19:43 Complex view question Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:04 ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:27   ` Re: Complex view question Josh Berkus <josh@agliodbs.com>
@ 2002-01-16 21:38     ` Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:47       ` Re: Complex view question Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Glenn MacGregor @ 2002-01-16 21:38 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

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" <josh@agliodbs.com>
To: "Glenn MacGregor" <gtm@oracom.com>; "Glenn MacGregor" <gtm@oracom.com>;
<pgsql-sql@postgresql.org>
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
>




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Complex view question
  2002-01-16 19:43 Complex view question Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:04 ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
  2002-01-16 21:27   ` Re: Complex view question Josh Berkus <josh@agliodbs.com>
  2002-01-16 21:38     ` Re: Complex view question Glenn MacGregor <gtm@oracom.com>
@ 2002-01-16 21:47       ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2002-01-16 21:47 UTC (permalink / raw)
  To: Glenn MacGregor <gtm@oracom.com>; Josh Berkus <josh@agliodbs.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-01-16 21:47 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-16 19:43 Complex view question Glenn MacGregor <gtm@oracom.com>
2002-01-16 21:04 ` Glenn MacGregor <gtm@oracom.com>
2002-01-16 21:27   ` Josh Berkus <josh@agliodbs.com>
2002-01-16 21:38     ` Glenn MacGregor <gtm@oracom.com>
2002-01-16 21:47       ` Josh Berkus <josh@agliodbs.com>

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