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:[EMAIL PROTECTED]] Sendt: 14. januar 2003 18:47 Til: Ignatius Reilly Cc: PHP-DB Emne: Re: [PHP-DB] 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" <[EMAIL PROTECTED]> >To: "Ignatius Reilly" <[EMAIL PROTECTED]> >Sent: Thursday, January 09, 2003 6:54 PM >Subject: Re: [PHP-DB] 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" <[EMAIL PROTECTED]> > > >To: "Ignatius Reilly" <[EMAIL PROTECTED]>; "PHP-DB" > > ><[EMAIL PROTECTED]> > > >Sent: Thursday, January 09, 2003 1:11 PM > > >Subject: Re: [PHP-DB] 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" <[EMAIL PROTECTED]> > > > > >To: "Ignatius Reilly" <[EMAIL PROTECTED]>; "PHP-DB" > > > > ><[EMAIL PROTECTED]> > > > > >Sent: Thursday, January 09, 2003 12:18 PM > > > > >Subject: Re: [PHP-DB] 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" <[EMAIL PROTECTED]> > > > > > > >To: "PHP-DB" <[EMAIL PROTECTED]> > > > > > > >Sent: Thursday, January 09, 2003 10:44 AM > > > > > > >Subject: [PHP-DB] 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
