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: 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
