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
>