Re: Popular affiliates query problem

Hmm.. I think I see the problem with the query I suggested. If you move the date condition from the WHERE clause to the LEFT JOIN ON clause then that should solve your problem.

SELECT A.ID
FROM AFFILIATE A
LEFT JOIN VISIT_AFFILIATE VA
ON A.ID = VA.AFFILIATEID
AND VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI') GROUP BY (A.ID)
ORDER BY COUNT(DISTINCT VA.USERID) DESC Another method is to use a subselect and a UNION, first select all affiliates which have visits (inner join) and then UNION it with all affiliates without wisits (all records in AFFILIATE which aren't in the first list (subquery)).

SELECT A.ID
FROM AFFILIATE A
INNER JOIN VISIT_AFFILIATE VA
ON A.ID = VA.AFFILIATEID
WHERE VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI') GROUP BY (A.ID)
ORDER BY COUNT(DISTINCT VA.USERID) DESC
UNION
SELECT ID FROM AFFILIATE
WHERE ID NOT IN (
  SELECT A.ID
  FROM AFFILIATE A
  INNER JOIN VISIT_AFFILIATE VA
  ON A.ID = VA.AFFILIATEID
  WHERE VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI') )

Hope this helps;
Helgi Received on Sat Nov 24 2001 - 14:38:44 CST