Using INNER JOIN to receive crossed data by date range

I have a theme gallery. In the dashboard i have to display the most viewed themes BY date (today, last 7 days, last 30 days, all time).

These are the 2 involved tables:

1. theme
   • id_theme
   • title
2. views
   • id_view
   • id_theme
   • date

The $timestamp values are calculated with mktime() (no prob in there).

This is my current SQL query:

 SELECT t.id_theme,t.title,
        (SELECT COUNT(*)
        FROM views
        WHERE views.id_theme=t.id_theme
        AND views.date BETWEEN '.$timestamp1.' AND '.$timestamp2.')
 AS q
 FROM theme AS t
 INNER JOIN views ON t.id_theme = views.id_theme
 GROUP BY views.id_theme
 ORDER BY q 
开发者_如何学Go DESC LIMIT 10

The problem is that The catch, is that sometimes it receives themes with 0 views, and that should not happen. I tried changing the INNER JOIN with RIGHT JOIN with no results. Any ideas?

Hmm. not sure why you're using subqueries for this, seems like this would work better:

SELECT theme.id_theme, theme.title, COUNT(views.id_view) as view_count
FROM theme
LEFT JOIN views ON (theme.id_theme = views.id_theme)
GROUP BY theme.id_theme
WHERE views.date > DATE_SUB(now() INTERVAL 30 day)
ORDER BY view_count DESC
HAVING view_count > 0

 SELECT t.id_theme, t.title, COUNT(*) AS q
 FROM theme AS t
 INNER JOIN views ON t.id_theme = views.id_theme 
    AND views.date BETWEEN '.$timestamp1.' AND '.$timestamp2.'
 GROUP BY t.id_theme, t.title
 ORDER BY q 
 DESC LIMIT 10