Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0728B632C52
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 17:41:07 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 71256-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 20:40:57 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mmp1.nems.noaa.gov (mmp1.nems.noaa.gov [140.90.121.156])
	by mail.postgresql.org (Postfix) with ESMTP id 0EADF632959
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 17:40:57 -0300 (ADT)
Received: from [198.206.42.50] by mmp1.nems.noaa.gov
	(Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005))
	with ESMTPSA id <0KOP00FZ10465000@mmp1.nems.noaa.gov> for
	pgsql-sql@postgresql.org; Thu, 20 Aug 2009 16:40:55 -0400 (EDT)
Date: Thu, 20 Aug 2009 16:40:55 -0400
From: Mark Fenbers <Mark.Fenbers@noaa.gov>
Subject: Re: Rewrite without correlated subqueries
In-reply-to: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-sql@postgresql.org
Message-id: <4A8DB4D7.6030102@noaa.gov>
Organization: DOC/NOAA/NWS/OHRFC
MIME-version: 1.0
Content-type: text/html; charset=UTF-8
Content-transfer-encoding: 8BIT
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-5.141 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457,
	RCVD_IN_DNSWL_MED=-4
X-Spam-Level: 
X-Archive-Number: 200908/115
X-Sequence-Number: 33024

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Try putting your subqueries into temporary tables, first, inside a
BEGIN ... COMMIT block.  But your subqueries would produce the
negative, i.e., everything except where sitescategory.idsites =
ps.idsites.  Then reference these temp tables in your query with inner
or outer joins as appropriate.  Your new query would not include the
... IN ( &lt;list&gt; ) syntax...<br>
<br>
Mark<br>
<br>
bricklen wrote:
<blockquote
 cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"
 type="cite">Hi All,<br>
  <br>
I'm having some trouble wrapping my head around the syntax to rewrite a
query using correlated subqueries, to using outer joins etc.<br>
  <br>
The query:<br>
  <br>
SELECT  ps.userid,<br>
               SUM( ps.hits ) as numhits<br>
FROM primarystats AS ps<br>
  INNER JOIN camp ON <a moz-do-not-send="true" href="http://camp.id"
 target="_blank">camp.id</a> = ps.idcamp<br>
  INNER JOIN sites ON <a moz-do-not-send="true" href="http://sites.id"
 target="_blank">sites.id</a> = ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
GROUP BY ps.userid;<br>
  <br>
Because I am rewriting this query to use Greenplum, I cannot use
correlated subqueries (they are not currently supported).<br>
  <br>
Can anyone suggest a version that will garner the same results? I tried
with OUTER JOINS and some IS NULLs, but I couldn't get it right.<br>
  <br>
Thanks!<br>
  <br>
bricklen<br>
  <br>
  <br>
</blockquote>
</body>
</html>