From: bricklen <bricklen@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Rewrite without correlated subqueries
Date: Thu, 20 Aug 2009 13:32:54 -0700
Message-ID: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com> (raw)
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
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: bricklen@gmail.com
Subject: Re: Rewrite without correlated subqueries
In-Reply-To: <33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com>
* 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