OK, this helped a bit. Now I have, in addition to the three rows of ads that have ben clicked on, a fourth row with no ad name, 0 clickthroughs, and 24 displays. That plus the other three account for all the displayed ads.

However, since it is returning a null value for any ad name that has not been clicked on, and then it's grouped by ad name, it lumps all non-clicked ads into one row. What I need is to see each ad on a separate row, which is what I thought a LEFT JOIN was supposed to do.

Any suggestions?



At 11:07 AM 1/9/03 +0100, Ignatius Reilly wrote:
problem 1:
move the WHERE clauses to the ON clauses

problem 2:
Obviously your intent with " COUNT(ads_clickrate.date) " is to count the
number of non-null occurences of click. But COUNT() is not the appropriate
function to do this (it will simply give you the number of rows inside a

Try replacing COUNT(ads_clickrate.date) by SUM( IF( ads_clickrate.date IS
NULL, 0, 1 ) )

----- Original Message -----
From: "Lisi" <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 10:44 AM
Subject: [PHP-DB] LEFT JOIN not working

> I have a page with many ads that stores both the number of times an ad is
> displayed and how many times it gets clicked.  These are stored in two
> different tables (since different information is stored for each) but both
> have identical name columns. I am trying to display both # times displayed
> and # times clicked in the same table in an admin page.
> Here is my query:
> SELECT ads_clickrate.name, ads_clickrate.link, SUM(ads_displayrate.count)
> as display, COUNT(ads_clickrate.date) as click FROM ads_displayrate LEFT
> JOIN ads_clickrate ON ads_displayrate.name = ads_clickrate.name WHERE
> YEAR(ads_clickrate.date) = '2003' AND MONTH(ads_clickrate.date) = '01'
> GROUP BY ads_clickrate.name ORDER BY ads_clickrate.name
> I want to display for each ad the number of times displayed, and then
> number of times clicked if applicable, if not 0.
> The query is only returning rows for ads that have been clicked on. Is the
> problem because I have a COUNT column for one table, with a group by? Is
> this causing the display to be grouped also?
> If you need more information how the table is set up let me know, I'm
> really stumped here.
> Thanks,
> -Lisi
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to