SV: LEFT JOIN not working

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux