OK, I'm coming back to this problem after not touching it for a while. Sorry about the indentation, it's a dynamically generated query and I just cut and pasted into the email.
I tried your suggestion, and it is showing the correct number of displays for most ads, but it is not limiting the clicks - it's displaying all clicks even if there were none for that day. Plus, for one ad, it is showing the two clicks (on a different day) it got plus it doubled the number of displays it had for that day - i.e. it is multiplying the 13 displays by the 2 clicks! Huh?????
Is this just too complicated to do with one query? Should I perhaps use different queries, and store the info somehow in an array by name, and display it that way?
I'm really stumped here.
Thanks,
-Lisi
At 07:31 AM 1/20/03 -0500, Bill Easton wrote:
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
