Re: LEFT JOIN not working

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

 



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


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

  Powered by Linux