Popular affiliates query problem

From: Jacob Nordgren <jacob_nordgren_at_hotmail.com>
Date: 22 Nov 2001 02:06:41 -0800
Message-ID: <47c6b9be.0111220206.3285baed@posting.google.com>

Hi all,

The following query finds out what affiliates are the most popular ones.

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 and when I add the line

AND VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI') 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.

What do you think? Can you help me change the query?

This is the look of the tables:

AFFILIATE
ID*, URL USER
ID*

VISIT_AFFILIATE
USERID*, AFFILIATEID*, MYDATE*
(a certain user visits an affiliate a certain day and time)

'*' means PK

Thank you!

/ Jacob Received on Thu Nov 22 2001 - 04:06:41 CST