Re: Popular affiliates query problem

What you are looking for is a LEFT JOIN, with the AFFILIATE table as the left table and focusing on ID instead of AFFILIATEID

SELECT A.ID
FROM AFFILIATE A
LEFT JOIN VISIT_AFFILIATE VA
ON A.ID = VA.AFFILIATEID
GROUP BY (A.ID)
ORDER BY COUNT(DISTINCT VA.USERID) DESC When you add the WHERE clause with the date criterion then you probably have to add an OR criterion also for the NULL records:

WHERE VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI')   OR VA.MYDATE IS NULL Regards,
Helgi Thor Agustsson

"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0111220206.3285baed_at_posting.google.com...
> SELECT VA.AFFILIATEID FROM VISIT_AFFILIATE VA, AFFILIATE V
> WHERE VA.AFFILIATEID = A.ID
> GROUP BY (VA.AFFILIATEID)
> ORDER BY COUNT(DISTINCT VA.USERID) DESC
 [snip]
> I only get the most popular affiliates after a certain date. If there
> are no users that has visited any affiliates during that period the
> list gets empty.
>
> I would like to change the query so that I always get a list of
> affiliates, even if there are no visits during the period. I think the
> solution is to exclude the date-part of the query and order by how
> many visits there were after that period. That means that the
> affiliates that didn't get any visits get 0 (zero) visits.
>
Received on Thu Nov 22 2001 - 17:01:21 CST