Re: Rewrite without correlated subqueries

Try putting your subqueries into temporary tables, first, inside a BEGIN ... COMMIT block.  But your subqueries would produce the negative, i.e., everything except where sitescategory.idsites = ps.idsites.  Then reference these temp tables in your query with inner or outer joins as appropriate.  Your new query would not include the ... IN ( <list> ) syntax...

Mark

bricklen wrote:

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