Re: [PHP-DB] LEFT JOIN not working
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" <[EMAIL PROTECTED]> To: "Ignatius Reilly" <[EMAIL PROTECTED]> Cc: "PHP-DB" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:56 AM Subject: SV: [PHP-DB] 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:[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
SV: [PHP-DB] 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:[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
Re: [PHP-DB] LEFT JOIN not working
For a start simplify the query, so that you are returning only one field until you get it right, say ads_displayrate.name. 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" <[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
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 >
Re: [PHP-DB] LEFT JOIN not working
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: > > > > &g
Re: [PHP-DB] LEFT JOIN not working
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.dat
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
Re: [PHP-DB] LEFT JOIN not working
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
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
Re: [PHP-DB] LEFT JOIN not working
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-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
