Re: Popular affiliates query problem

From: Brian Dick <bdick_at_home.com>
Date: Mon, 26 Nov 2001 14:14:28 GMT
Message-ID: <87sM7.19085$Xb7.142937@news1.wwck1.ri.home.com>

> Jacob Nordgren wrote:
>
> > 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

Try something like:

select a.id
from affiliate a,

    (select affiliateid, count(*) visit_cnt

     from visit_affiliate
     where mydate >= TO_DATE('2001-11-22 11:00, 'YYYY-MM-DD HH:MI')) va

where a.id = va.affiliateid (+)
order by nvl(va.visit_cnt, 0) desc Received on Mon Nov 26 2001 - 08:14:28 CST