Re: LEFT JOIN not working

Exactly my question - why does it not have a name? How would I modify my query to get a row with a name but null value for date? I thought the join would take care of this, but I'm obviously not doing it right. You mention a unique identifier, there is a separate table with a row for each ad, containing name, URL, and a unique ID number (autoincrement). Should this table be included somehow in the query? How would this help? Thanks, -Lisi At 12:45 PM 1/9/03 +0100, Ignatius Reilly wrote:

Your 4th row ought to have an identifier of some sort. From your SELECT statement, this seems to be the name. Why does it not have a name? Probably what you want is a row with a name but a NULL value for ads_clickrate.date. (by the way it is EXTREMELY advisable to use an abstract identifier, such as an id, unique and required, instead of "name") Ignatius ____________________________________________ ----- Original Message ----- From: "Lisi" <lists@shemeshdirectory.co.il> To: "Ignatius Reilly" <ignatius.reilly@free.fr>; "PHP-DB" <php-db@lists.php.net> Sent: Thursday, January 09, 2003 12:18 PM Subject: Re: [PHP-DB] LEFT JOIN not working 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? 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: [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