Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 0F04A633F2E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 18:16:17 -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 82177-07 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 20 Aug 2009 21:16:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24])
	by mail.postgresql.org (Postfix) with ESMTP id D8C8B62E9C6
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 18:16:06 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 3so125437qwe.61
	for <pgsql-sql@postgresql.org>; Thu, 20 Aug 2009 14:16:05 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=wpksBq5KYnrQtl18AoAGtAC0ZFic4N8O/Ui3ewMZ22U=;
	b=DrYRTW8h5S9TKVz5+UYhkmXxcDnP2jZF/5d/A3RYnEYMqeL/x4S41CNrpFUDWva1Fo
	Rs/fEbYyzQNryZPVePlKZp9auAJ2cebn+2wZ0SBijh9EEe7bJT1Ym+8W8iwVng2L02xT
	LeIffLT/k+1314eiOH2ol+ktUYJILquH5cYHI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=QImHTnsBE6T/zxSU/ibjqqf5xS+iyO29fB3RQUY1s61ZMcN3Sv6qkrz9fJlBLRN3wP
	48ua8lDfql0YACOBR6Tf3Cd+VMQbfB/nEX41LOzQfWy45OvH+7AdldQF09Pv+Plvra+Z
	37Nfq4S3ovX0DasZhifUSOpknrVEccfluoxjI=
MIME-Version: 1.0
Received: by 10.229.55.69 with SMTP id t5mr57382qcg.34.1250802964701; Thu, 20
	Aug 2009 14:16:04 -0700 (PDT)
In-Reply-To: <4A8DB4D7.6030102@noaa.gov>
References: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
	<4A8DB4D7.6030102@noaa.gov>
Date: Thu, 20 Aug 2009 14:16:04 -0700
Message-ID: <33b743250908201416h3ec8b61cp4976ab540c4e7cee@mail.gmail.com>
Subject: Re: Rewrite without correlated subqueries
From: bricklen <bricklen@gmail.com>
To: Mark Fenbers <Mark.Fenbers@noaa.gov>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015175dd8b86524c504719942a8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200908/116
X-Sequence-Number: 33025

--0015175dd8b86524c504719942a8
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Interesting idea. Preferably this operation could be done in straight SQL in
a single transaction, to fit in with the way our application works, but if
that's not possible I may need to go the temporary table route.

On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:

>  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 ( <list> ) syntax...
>
> Mark
>
>
> bricklen wrote:
>
> Hi All,
>
> I'm having some trouble wrapping my head around the syntax to rewrite a
> query using correlated subqueries, to using outer joins etc.
>
> The query:
>
> SELECT  ps.userid,
>                SUM( ps.hits ) as numhits
> FROM primarystats AS ps
>   INNER JOIN camp ON camp.id = ps.idcamp
>   INNER JOIN sites ON sites.id = ps.idsite
> WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE
> sitescategory.idsites = ps.idsites )
> GROUP BY ps.userid;
>
> Because I am rewriting this query to use Greenplum, I cannot use correlated
> subqueries (they are not currently supported).
>
> 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.
>
> Thanks!
>
> bricklen
>
>
>

--0015175dd8b86524c504719942a8
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Interesting idea. Preferably this operation could be done in straight
SQL in a single transaction, to fit in with the way our application
works, but if that&#39;s not possible I may need to go the temporary table
route.<br><br><div class=3D"gmail_quote">On Thu, Aug 20, 2009 at 1:40 PM, M=
ark Fenbers <span dir=3D"ltr">&lt;<a href=3D"mailto:Mark.Fenbers@noaa.gov">=
Mark.Fenbers@noaa.gov</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_q=
uote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0=
pt 0.8ex; padding-left: 1ex;">



 =20

<div bgcolor=3D"#ffffff" text=3D"#000000">
Try putting your subqueries into temporary tables, first, inside a
BEGIN ... COMMIT block.=C2=A0 But your subqueries would produce the
negative, i.e., everything except where sitescategory.idsites =3D
ps.idsites.=C2=A0 Then reference these temp tables in your query with inner
or outer joins as appropriate.=C2=A0 Your new query would not include the
... IN ( &lt;list&gt; ) syntax...<br><font color=3D"#888888">
<br>
Mark</font><div><div></div><div class=3D"h5"><br>
<br>
bricklen wrote:
<blockquote type=3D"cite">Hi All,<br>
  <br>
I&#39;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=C2=A0 ps.userid,<br>
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 SUM( ps.hits ) as numhits<br>
FROM primarystats AS ps<br>
=C2=A0 INNER JOIN camp ON <a href=3D"http://camp.id" target=3D"_blank">camp=
.id</a> =3D ps.idcamp<br>
=C2=A0 INNER JOIN sites ON <a href=3D"http://sites.id" target=3D"_blank">si=
tes.id</a> =3D ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites =3D ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites =3D 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&#39;t get it right.<br>
  <br>
Thanks!<br>
  <br>
bricklen<br>
  <br>
  <br>
</blockquote>
</div></div></div>

</blockquote></div><br>

--0015175dd8b86524c504719942a8--