Re: LEFT JOIN not working

For a start simplify the query, so that you are returning only one field until you get it right, say ads_displayrate.name. Second, echo the SQL statement so you can see what has been generated. Third, check the datatypes returned for YEAR(), MOTN() and DAYOFMONTH() functions in MySQL. As part of your diagnosis you may want to include the generated output from these functions so you see what they are returning. Fourth, if you have access to the MySQL console, try this interactively. HTH - Miles Thompson At 07:46 PM 1/14/2003 +0200, Lisi wrote:

Still not working. I made the change, and I'm still getting all results. I even tried it without the leading '0' in front of the 1, no good. Here's my current query, with suggested changes: SELECT ads_displayrate.name, SUM(ads_displayrate.count) as display, SUM( IF( ads_clickrate.date IS NULL, 0, 1 ) ) as click FROM ads_displayrate LEFT JOIN ads_clickrate ON ads_displayrate.name = ads_clickrate.name AND YEAR(ads_displayrate.date) = '2003' AND MONTH(ads_displayrate.date) = '01' AND DAYOFMONTH(ads_displayrate.date) = '05' GROUP BY ads_displayrate.name ORDER BY ads_displayrate.name ads_displayrate.date is a column of date type, so as far as I understand this should work. Is there some typo I'm missing? At 07:15 PM 1/9/03 +0100, you wrote:

Oops! I missed again. If you specify conditions pertaining to the right-hand table, such as: ads_clickrate.date = '2001', Then you will lose all result rows for which the right-hand data is NULL. Not the expected result. So your restricting WHERE clauses must apply to the left-hand table only. Therefore: WHERE YEAR(ads_displayrate.date) = '2003' AND MONTH(ads_displayrate.date) = '01' (if your table ads_displayrate has such date fields). HTH Ignatius ____________________________________________ ----- Original Message ----- From: "Lisi" <lists@shemeshdirectory.co.il> To: "Ignatius Reilly" <ignatius.reilly@free.fr> Sent: Thursday, January 09, 2003 6:54 PM Subject: Re: [PHP-DB] LEFT JOIN not working

Cool! It's mostly working now, the only problem is it's ignoring the other clauses in the ON clause that select the desired date. Perhaps it's not supposed to be connected this way? How would I select specific dates? Thanks again, -Lisi At 01:20 PM 1/9/03 +0100, you wrote:

Oops! Sorry, I missed it the first time. Your query should start as: SELECT ads_displayrate.name instead of SELECT ads_clickrate.name then you will always have a non-NULL name (coming from the table on the left of the LEFT JOIN). HTH Ignatius, from Brussels "Where the fuck is Belgium?" D. Ivester, CEO, Coca Cola ____________________________________________ ----- 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 1:11 PM Subject: Re: [PHP-DB] 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

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