Re: Popular affiliates query problem
Date: Thu, 22 Nov 2001 23:01:21 -0000
Message-ID: <9tk04a$183k$1_at_mikill.isnet.is>
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
WHERE VA.MYDATE >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI')
OR VA.MYDATE IS NULL
Regards,
"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message
news:47c6b9be.0111220206.3285baed_at_posting.google.com...
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:
Helgi Thor Agustsson
> 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 Fri Nov 23 2001 - 00:01:21 CET