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
