Should work (provided that the HAVING clauses be written after the GROUP BY) But less efficient. The point of using the WHERE clauses is to restrict during the JOIN. What you propose amounts to joining the full left table, which may be very expensive, especially in the case of web log data. Ignatius ____________________________________________ ----- Original Message ----- From: "Henrik Hornemann" <HEH@dnlb.dk> To: "Ignatius Reilly" <ignatius.reilly@free.fr> Cc: "PHP-DB" <php-db@lists.php.net> Sent: Wednesday, January 15, 2003 10:56 AM Subject: SV: LEFT JOIN not working > Hi, > > You might want to try something like: > > 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 > HAVING 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 > > regards Henrik Hornemann > > -----Oprindelig meddelelse----- > Fra: Lisi [mailto:lists@shemeshdirectory.co.il] > Sendt: 14. januar 2003 18:47 > Til: Ignatius Reilly > Cc: PHP-DB > Emne: Re: LEFT JOIN not working > > > 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: 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: 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: 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: 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 > > -- > 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
