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.fenb...@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 > > >
