Re: Popular affiliates query problem

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

Your result isn't a table because it has 'essential ordering'; there is no way from looking at the returned data that you can tell why a given row appears where it does in the list.

However, that's mostly by the bye.

You want the UNION of two distinct queries, one with the affiliates who have one or more visits in the given period (that's what you've got already), and the other with the affiliates who have no visits in the given period. You might have to fix up the ORDER BY clause (and maybe even the select-list) since you usually have to specify column numbers in the ORDER BY associated with a UNION, and you usually can't specify column 2 when you only select one column. This harps back to my original point. FWIW: Informix does not yet allow you to order by a column that is not in the select-list. This adheres to the letter of at least one of the old SQL standards -- I believe it conforms to entry-level SQL-92, but I'd accept a correction that said "No, SQL-89, meaning SQL-86 plus referential integrity addendum".

--
Jonathan Leffler (jleffler_at_earthlink.net, jleffler_at_informix.com)
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include <disclaimer.h>