Popular affiliates query problem
Date: 22 Nov 2001 02:06:41 -0800
Message-ID: <47c6b9be.0111220206.3285baed_at_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
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.
GROUP BY (VA.AFFILIATEID)
ORDER BY COUNT(DISTINCT VA.USERID) DESC
and when I add the line
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 - 11:06:41 CET