Received: from degroens.oracom.com (degroens.oracom.com [63.238.138.68]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0GLX2V20921 for ; Wed, 16 Jan 2002 16:33:02 -0500 (EST) (envelope-from gtm@oracom.com) Received: (qmail 1829 invoked from network); 16 Jan 2002 17:41:59 -0000 Received: from unknown (HELO catamount) (192.168.253.253) by 192.168.253.2 with SMTP; 16 Jan 2002 17:41:59 -0000 Message-ID: <034a01c19ed6$135c2380$4d00a8c0@catamount> From: "Glenn MacGregor" To: "Josh Berkus" , References: Subject: Re: Complex view question Date: Wed, 16 Jan 2002 16:38:02 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200201/195 X-Sequence-Number: 6281 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" To: "Glenn MacGregor" ; "Glenn MacGregor" ; 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 >