Re: Rewrite without correlated subqueries

Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block.  But your subqueries would
producethe negative, i.e., everything except where sitescategory.idsites = ps.idsites.  Then reference these temp
tablesin your query with inner or outer joins as appropriate.  Your new query would not include the ... IN (
<list>) syntax...<br /><br /> Mark<br /><br /> bricklen wrote: <blockquote
cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a@mail.gmail.com"type="cite">Hi All,<br /><br /> I'm having some
troublewrapping 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
primarystatsAS ps<br />   INNER JOIN camp ON <a href="http://camp.id" moz-do-not-send="true"
target="_blank">camp.id</a>= ps.idcamp<br />   INNER JOIN sites ON <a href="http://sites.id" moz-do-not-send="true"
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
useGreenplum, 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 /></blockquote>