Using INNER JOIN to Retrieve Crossed Date Data

Question

I have a theme gallery. On the dashboard, I should display the most viewed topics by date (today, last 7 days, last 30 days, all the time).

These are the two tables involved:

• subject
  • id_theme
  • title
• view
  • id_view
  • id_theme
  • the date

The values ​​of $ timestamp are calculated using mktime () (there is no problem 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 
 DESC LIMIT 10

The problem is that "Catch" is that sometimes it gets topics with 0 views, and this should not happen. I tried changing INNER JOIN with RIGHT JOIN without any results. Any ideas?

Answer 1

Hm. not sure why you use subqueries for this, it looks like this will 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

Answer 2

 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