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.Fenbers@noaa.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