Shalom, Lisi,
(Sure would have been nice if you had indented and simplified your
SQL statements so they could be read by a mere human instead of
just by a computer ;-})
You have
SELECT ...
FROM display
LEFT JOIN click
ON display.name=click.name
AND ...
AND DAYOFMONTH(display.date) = '19';
The result of this select consists of the following:
(1) The result of the following inner join:
SELECT ...
FROM display. click
WHERE display.name=click.name
AND ...
AND DAYOFMONTH(display.date) = '19';
(2) For each row of display that did not get used in (1),
that row together with nulls for all columns of click.
In short, you get at least one row for each row of display--the ON
clause only affects which ones that have data from click. That's
how LEFT JOIN works. So, in particular, you get data for the
whole month.
You probably want something like:
SELECT ...
FROM display
LEFT JOIN click
ON display.name=click.name
WHERE ...
AND DAYOFMONTH(display.date) = '19';
The left join will give you rows with data from both tables and rows
from display that don't have data in click; the where clause will
then narrow the selection to the day you want.
Hope this helps
> Date: Sun, 19 Jan 2003 19:02:25 +0200
> To: [EMAIL PROTECTED]
> From: Lisi <[EMAIL PROTECTED]>
> Subject: Problem with LEFT JOIN
> 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 to find ads that were clicked on today:
> 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) = '19' GROUP BY ads_displayrate.name
> ORDER BY ads_displayrate.name
> This works for clicks, but no matter what date I put in it only shows
> displays for the whole month - not the selected day. Also, if I use the
> following query to find clicks for the whole month
> 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'
> GROUP BY ads_displayrate.name ORDER BY ads_displayrate.name
> it doubles the number from what it should be.
> What am I doing wrong?
> Thanks in advance,
> -Lisi
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
