Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 703A7632D04 for ; Thu, 20 Aug 2009 17:33: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 70045-06 for ; Thu, 20 Aug 2009 20:32:57 +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.27]) by mail.postgresql.org (Postfix) with ESMTP id 6A118635300 for ; Thu, 20 Aug 2009 17:32:57 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 3so107207qwe.61 for ; Thu, 20 Aug 2009 13:32:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=B/hGQfoZkp4kA/goMc5jMSMTKsj0QjomKnoMolQSUuI=; b=qS8LuocNxQvvW4d/LdA2VA8hql59AwHVOCYTNLJHqp6Je0BDU7g4PSRo2GeNH7SPl0 gOQuIRolYIwWnTBnZ9fA8IdHhMu7j3Pw+qHgRc+NIgPbEVGX8Hr1/Fk3gKQNQvFQNf/Z FlGh02xdjnrGE564uGjl3aCNknOvmKZd5FmkE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=BLarvXqDm3EjoQbM/JOiuH+/vG25C80O7m1FSTDyvAlrdbfVnTM9yCVCGKVB3tNAgk 2gNCGE1QxO8visaz9I0CU8v14OSqCZGekVBU11WrrOK65ofEXO2aK2jxUV3UjrFsQa83 nv+H9BjdQ9YRWV59LVwxQp6Mb7wUliCBYAXSk= MIME-Version: 1.0 Received: by 10.229.54.78 with SMTP id p14mr45756qcg.67.1250800375018; Thu, 20 Aug 2009 13:32:55 -0700 (PDT) Date: Thu, 20 Aug 2009 13:32:54 -0700 Message-ID: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com> Subject: Rewrite without correlated subqueries From: bricklen To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=00151773e6ba09b4f5047198a828 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/114 X-Sequence-Number: 33023 --00151773e6ba09b4f5047198a828 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 --00151773e6ba09b4f5047198a828 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi All,

I'm having some trouble wrapping my head around the synt= ax to rewrite a query using correlated subqueries, to using outer joins etc= .

The query:

SELECT=C2=A0 ps.userid,
=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.hit= s ) as numhits
FROM primarystats AS ps
=C2=A0 INNER JOIN camp ON camp.id =3D ps.idcamp
=C2=A0 INNER JOIN sites= ON sites.id =3D ps.idsit= e
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory = WHERE sitescategory.idsites =3D ps.idsites )
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory WHERE= sitescategory.idsites =3D ps.idsites )
GROUP BY ps.userid;

Becau= se I am rewriting this query to use Greenplum, I cannot use correlated subq= ueries (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


--00151773e6ba09b4f5047198a828--