Wednesday 5 September 2018

MySQL - LEFT JOIN and COUNT ()

I have 3 tables:

  1. forums
id, name, description
  1. threads
thread_id, forum_id, user_id, title, content, views
  1. posts
post_id, thread_id, author_id, content, date
What I want to do is to get all the threads in a forum, and get the post count of every thread. So I get every thread (WHERE forum_id = whatever) and then I LEFT JOIN with the table posts so in order to count the results. But something is not working. Here is my query:
SELECT t.*, u.nick, COUNT(p.post_id) AS postcount
  FROM
    threads t
  LEFT JOIN
    users u
    ON
       u.id = t.user_id
  LEFT JOIN
     posts p
     ON
       p.thread_id = t.thread_id
  WHERE
     t.forum_id = $this->forumID

This query will only show (I think) the threads that have any post on it. I also tried using the GROUP BY statement but it makes MySQL error...
How can I solve this?
----------- EDIT: I tried adding GROUP BY t.thread_id, however, as I said before, MySQL errors:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t.forum_id = 2' at line 15
Full query:
SELECT t.*, u.nick, COUNT(p.post_id) AS postcount
  FROM
    threads t
  LEFT JOIN
    users u
    ON
       u.id = t.user_id
  LEFT JOIN
     posts p
     ON
       p.thread_id = t.thread_id
  GROUP BY
     t.thread_id
  WHERE
     t.forum_id = $this->forumID

EDIT 2:
My bad, I put the GROUP BY statement where it wasn't meant to be. It is now solved.

GROUP BY was the right way to go, so just add: GROUP BY t.thread_id

0 comments:

Post a Comment