It worked! Thanks so much for your help, and for your suggestions about formatting queries. I haven't worked much with complicated queries, and this was my first time posting to this list.
Thanks for the help!
-Lisi
At 05:11 AM 2/8/03 -0500, Bill Easton wrote:
Lisi, First, limiting the clicks: You didn't have any condition on click.date in your suggested queries. You need to have one. Here's my suggestion, somewhat modified. Note that the limit on click.date goes in the ON clause, while the limit on display.date goes in the WHERE clause. Looks wierd until you think about the meaning of left join. The left join gives you all displays, together with clicks for the day you want and nulls for displays with no clicks. The where clause then throws away displays for the wrong days.SELECT ... FROM display LEFT JOIN click ON display.name=click.name AND ... AND DAYOFMONTH(click.date) = '19' WHERE ... AND DAYOFMONTH(display.date) = '19'; Now, about the counts. The above will give you a (display, click) pair for every display and click with the same name, plus a (display, null) for the displays without clicks. Summing counts of the (display, click) pairs is going to give you results that are wrong, as you found out. Can you do it in one query? Probably not, until MySQL gets subqueries. Why not use a temporary table? CREATE TEMPORARY TABLE dcounts SELECT display.name, SUM(display.count) as dc WHERE ... AND DAYOFMONTH(display.date) = '19' GROUP BY display.name; this gives you a table of displays and counts. Then use a LEFT JOIN to combine it with click: SELECT dcounts.name, dc, SUM(clicks.date IS NOT NULL) FROM dcounts LEFT JOIN click ON dcounts.name=click.name AND ... AND DAYOFMONTH(click.date) = '19' Note: If you are replicating, you may want to create the temporary tables in a second database and use binlog-ignore-db to avoid cluttering up the logs and to avoid having the slave get confused by having the same table created in multiple threads on the server. Subqueries will sure make life simpler. About my comment on indenting. Yes, you cut and pasted from a generated query. What I asked is that, before sending the message, you insert some new lines and some indentation. You're asking for free help, and you're more likely to get it if people don't have to spend time figuring out what the question is. ----- Original Message ----- From: "Lisi" <[EMAIL PROTECTED]> To: "Bill Easton" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, February 03, 2003 3:37 AM Subject: Re: Problem with LEFT JOIN > > 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
