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

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

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

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" <bbois@hotmail.com> wrote in message
news:1135294076.263000.15890@g14g2000cwa.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.

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

Cheers,
Bill

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
>