How do I get this mysql query to include null values?

Question

Im writing a pruning script, to delete content from my site that was uploaded over a week ago, and been accessed 0 or 1 times, also in the last week.

there are 2 tables:

• daily_hits - which stores the item id, date, and number of hits that item got on that date.
• videos - stores actual content

I came up with this.

$last_week_date = date('Y-m-d',$now-(60*60*24*7));
$last_week_timestamp = $now-(60*60*24*7);

SQL

SELECT 
      vid_id, 
      COALESCE(sum(hit_hits),0) as total_hits 
     FROM videos
     LEFT JOIN daily_hits
      ON vid_id = hit_itemid 
     WHERE (hit_date >= '$last_week_date') AND vid_posttime <= '$last_week_timestamp' 
     GROUP BY hit_itemid 
     HAVING  total_hits < 2

This does output the items that were access once in the last week.... but not the ones that haven't been accessed at all. If an item wasn't accessed at all in that last week, there wont be any entries in the daily_hits table. I figured COALESE should take care of that, but that didnt work.

How can I fix this?

Answer 1

total_hits < 2

This guarantees "null hits" won't show up.

Make a second query that finds the nulls (it will show records from videos that have no corresponding key in daily_hits).

Make a UNION query to present the two datasets as one.

Answer 2

 HAVING  total_hits < 2 or total_hits is null