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 href="http://camp.id"
target="_blank">camp.id</a>= ps.idcamp<br /> INNER JOIN sites ON <a href="http://sites.id"
target="_blank">sites.id</a>= ps.idsite<br />WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.idsites = ps.idsites )<br /> AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHEREsitescategory.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
versionthat 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 />