SV: [PHP-DB] LEFT JOIN not working

[Henrik Hornemann]

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

Re: [PHP-DB] LEFT JOIN not working

[Ignatius Reilly]

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

Re: [PHP-DB] LEFT JOIN not working

[Lisi]

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

Re: [PHP-DB] LEFT JOIN not working

[Miles Thompson]

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

[PHP-DB] LEFT JOIN not working

[Lisi]

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

Re: [PHP-DB] LEFT JOIN not working

[Ignatius Reilly]

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

[Ignatius Reilly]

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

[Ignatius Reilly]

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

Re: [PHP-DB] LEFT JOIN not working

[Ignatius Reilly]

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