Re: Rewrite without correlated subqueries

Interesting idea. Preferably this operation could be done in straight SQL in
a single transaction, to fit in with the way our application works, but if
that's not possible I may need to go the temporary table route.

On Thu, Aug 20, 2009 at 1:40 PM, Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov> wrote:

> 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
>
>
>