pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>




view thread (5+ messages)  latest in thread

Message-ID: <034a01c19ed6$135c2380$4d00a8c0@catamount>
Permalink:  ../034a01c19ed6$135c2380$4d00a8c0@catamount/
Also on:    postgresql.org/message-id/034a01c19ed6$135c2380$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, 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