Re: Complex view question

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
>