Re: Popular affiliates query problem

Hi again,

Your query almost worked fine. However if the VISIT_AFFILIATE table contains only one row where AFFILIATEID is 1 and MYDATE is 2001-11-07 and USERID = 1 and the AFFILIATE table contains only one row where ID = 1 and I run this query:

SELECT A.ID
FROM AFFILIATE A, VISIT_AFFILIATE VA
WHERE A.ID = VA.AFFILIATEID(+)
AND (VA.MYDATE >= TO_DATE('2001-11-08', 'YYYY-MM-DD') OR VA.MYDATE IS NULL)
GROUP BY (A.ID)
ORDER BY COUNT(DISTINCT VA.USERID) DESC the result becomes empty.

I want to have a query where I always get a list of affiliates, even if there are no visits during the period. In other words: I want to order by a count on the number of visits (distinct users) during the period. If no visits, the count becomes 0 (zero). The big difference is that the affiliate will be a part of the result.

Thanks for your help,

/ Jacob

"Helgi ?r ??tsson" <helgith_at_itn.is> wrote in message news:<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
> 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 Fri Nov 23 2001 - 05:03:02 CST