<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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...<br>
<br>
Mark<br>
<br>
bricklen wrote:
<blockquote
cite="mid:33b743250908201332k5e7b0e3fga71a1ec6b44f302a(at)mail(dot)gmail(dot)com"
type="cite">Hi All,<br>
<br>
I'm having some trouble wrapping 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 primarystats AS ps<br>
INNER JOIN camp ON <a moz-do-not-send="true" href="http://camp.id"
target="_blank">camp.id</a> = ps.idcamp<br>
INNER JOIN sites ON <a moz-do-not-send="true" href="http://sites.id"
target="_blank">sites.id</a> = ps.idsite<br>
WHERE camp.idcatprimary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
AND camp.idcatsecondary NOT IN ( SELECT idcategory FROM sitescategory
WHERE sitescategory.idsites = ps.idsites )<br>
GROUP BY ps.userid;<br>
<br>
Because I am rewriting this query to use Greenplum, I cannot use
correlated subqueries (they are not currently supported).<br>
<br>
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.<br>
<br>
Thanks!<br>
<br>
bricklen<br>
<br>
<br>
</blockquote>
</body>
</html>