Professional Web Applications Themes

Left join isn't joining - MySQL

Hello, I've got a select statement that joins two tables. -- tblPageHitCalendar contains a single column holding dates. It has every day since our web site went live. -- tblPageHits has a row for every time a page was hit. I want this statement to tell me how many hits per day a page got in a specified date range. It does that, but if the page got no hits on a particular day, that day isn't returned. It should show the day and zero, but the day isn't returned at all. What have I done wrong here? Thanks, Bill ...

  1. #1

    Default Left join isn't joining

    Hello,

    I've got a select statement that joins two tables.
    -- tblPageHitCalendar contains a single column holding dates. It has
    every day since our web site went live.
    -- tblPageHits has a row for every time a page was hit.

    I want this statement to tell me how many hits per day a page got in a
    specified date range. It does that, but if the page got no hits on a
    particular day, that day isn't returned. It should show the day and
    zero, but the day isn't returned at all. What have I done wrong here?

    Thanks,
    Bill

    SELECT c.hitdate,
    COUNT(h.url)
    FROM tblPageHitCalendar c
    LEFT JOIN tblPageHits h ON c.hitdate = date_format(h.hittime,
    '%Y-%m-%d')
    WHERE c.hitdate BETWEEN '2005-04-01' AND '2005-07-31 23:59:59'
    AND h.url = '/mission.php'
    GROUP BY c.hitdate

    Bill Guest

  2. #2

    Default Re: Left join isn't joining

    If you require a value from both tables in the WHERE clause, you might
    as well use an inner join. The left join probably IS executed, but you
    filter out any empty records with your WHERE clause.

    Are you sure you don't mean a right join here?

    Bill wrote:
    > Hello,
    >
    > I've got a select statement that joins two tables.
    > -- tblPageHitCalendar contains a single column holding dates. It has
    > every day since our web site went live.
    > -- tblPageHits has a row for every time a page was hit.
    >
    > I want this statement to tell me how many hits per day a page got in a
    > specified date range. It does that, but if the page got no hits on a
    > particular day, that day isn't returned. It should show the day and
    > zero, but the day isn't returned at all. What have I done wrong here?
    >
    > Thanks,
    > Bill
    >
    > SELECT c.hitdate,
    > COUNT(h.url)
    > FROM tblPageHitCalendar c
    > LEFT JOIN tblPageHits h ON c.hitdate = date_format(h.hittime,
    > '%Y-%m-%d')
    > WHERE c.hitdate BETWEEN '2005-04-01' AND '2005-07-31 23:59:59'
    > AND h.url = '/mission.php'
    > GROUP BY c.hitdate
    >
    Dikkie Dik Guest

  3. #3

    Default Re: Left join isn't joining

    Dikkie Dik wrote:
    > If you require a value from both tables in the WHERE clause, you might
    > as well use an inner join. The left join probably IS executed, but you
    > filter out any empty records with your WHERE clause.
    >
    > Are you sure you don't mean a right join here?
    Yep, he wants the outer join.
    John A. Bailo Guest

  4. #4

    Default Re: Left join isn't joining

    "Bill" <bboishotmail.com> wrote in message
    news:1135294076.263000.15890g14g2000cwa.googlegro ups.com...
    > Hello,
    >
    > I've got a select statement that joins two tables.
    > -- tblPageHitCalendar contains a single column holding dates. It has
    > every day since our web site went live.
    > -- tblPageHits has a row for every time a page was hit.
    >
    > I want this statement to tell me how many hits per day a page got in a
    > specified date range. It does that, but if the page got no hits on a
    > particular day, that day isn't returned. It should show the day and
    > zero, but the day isn't returned at all. What have I done wrong here?
    >
    > Thanks,
    > Bill
    >
    > SELECT c.hitdate,
    > COUNT(h.url)
    > FROM tblPageHitCalendar c
    > LEFT JOIN tblPageHits h ON c.hitdate = date_format(h.hittime,
    > '%Y-%m-%d')
    > WHERE c.hitdate BETWEEN '2005-04-01' AND '2005-07-31 23:59:59'
    > AND h.url = '/mission.php'
    > GROUP BY c.hitdate
    It's because you put the condition h.url = '/mission.php' in the WHERE
    clause, which is evaluated after the join is performed.

    On days where there are no hits, NULL is the result of h.url. This is
    clearly not equal to '/mission.php', so the row of the result set is
    excluded and you don't get to see the day.

    The better way to restrict rows in the right-hand-side of a left outer join,
    put the condition inside the join condition clause.

    .... FROM tblPageHitCalendar c
    LEFT JOIN tblPageHits h ON (c.hitdate = date_format(h.hittime,
    '%Y-%m-%d') AND h.url = '/mission.php')
    WHERE c.hitdate BETWEEN '2005-04-01' AND '2005-07-31 23:59:59'

    It's okay to put conditions for tblPageHitCalendar in the WHERE clause,
    because it's on the left side of the join that is certain to have results.

    Regards,
    Bill K.


    Bill Karwin Guest

  5. #5

    Default Re: Left join isn't joining

    OK, that makes sense, but how would I specify the date range or page
    without a WHERE clause? I tried switching the WHERE to use the same
    table but it gives me the same result, probably because it's on the
    right side of the join.

    I also tried a right join and it returns exactly the same thing.

    Thanks!
    Bill

    Bill Guest

  6. #6

    Default Re: Left join isn't joining

    Wait, it's the HAVING clause, isn't it? I'll have to give it a try.
    It's been too long since I've played with SQL....

    Bill Guest

  7. #7

    Default Re: Left join isn't joining

    Beautiful! I get it now. Thanks for your help!

    Cheers,
    Bill

    Bill Guest

  8. #8

    Default Re: Left join isn't joining

    Use:

    .... ((c.hitdate BETWEEN '2005-04-01' AND '2005-07-31 23:59:59') OR
    c.hitdate IS NULL)

    if you want both the records with the hitdates in the range and the
    results for which no c.hitdate exists. Just try your query in steps and
    you see what happens. Try it without a WHERE clause, with a restriction
    on the c table and with restrictions on both tables.

    Bill wrote:
    > OK, that makes sense, but how would I specify the date range or page
    > without a WHERE clause? I tried switching the WHERE to use the same
    > table but it gives me the same result, probably because it's on the
    > right side of the join.
    >
    > I also tried a right join and it returns exactly the same thing.
    >
    > Thanks!
    > Bill
    >
    Dikkie Dik Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  3. left join limit 1
    By in forum MySQL
    Replies: 7
    Last Post: March 27th, 04:38 AM
  4. SQL query with a Left outer Join
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 04:48 AM
  5. left join problem
    By AK in forum IBM DB2
    Replies: 8
    Last Post: August 8th, 12:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139