From: Glenn MacGregor <gtm@oracom.com>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: Complex view question
Date: Wed, 16 Jan 2002 16:38:02 -0500
Message-ID: <034a01c19ed6$135c2380$4d00a8c0@catamount> (raw)
References: <web-621524@davinci.ethosmedia.com>
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
>
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, josh@agliodbs.com
Subject: Re: Complex view question
In-Reply-To: <034a01c19ed6$135c2380$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