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?
Thanks,
-Lisi
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 group). Try replacing COUNT(ads_clickrate.date) by SUM( IF( ads_clickrate.date IS NULL, 0, 1 ) ) HTH Ignatius ____________________________________________ ----- Original Message ----- From: "Lisi" <lists@shemeshdirectory.co.il> To: "PHP-DB" <php-db@lists.php.net> Sent: Thursday, January 09, 2003 10:44 AM Subject: 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
