DaviesTobi alex DaviesTobi alex - 3 months ago 19
MySQL Question

Selecting from three tables

I am trying to

SELECT
from one table and count from two other tables based on the rows from the first table. I tried the following code below but the rows keep coming empty.

SELECT list.id, list.title, list.body, list.poster, list.created_at, count(comments.id) as comcount, count(supports.topic_id) as supcount
FROM (
SELECT *
FROM topics
ORDER BY created_at DESC
LIMIT 5
) AS list, comments, supports
WHERE
list.id = comments.id OR
list.id = supports.topic_id


Through in this scenario table
topics
has only two rows and tables
comments
and
supports
have no rows in them, but yet still I should be able to get two rows with their aliases
supcount
and
comcount
each having a value
0
as an output.

Answer

This gonna be working, give a try (using subquery for just counting entries in another table is more suitable):

SELECT 
  id,
  title,
  body,
  poster,
  created_at,
  IFNULL((SELECT COUNT(*) FROM comments c WHERE c.topic_id = t.id), 0) AS comcount,
  IFNULL((SELECT COUNT(*) FROM supports s WHERE s.topic_id = t.id), 0) AS supcount
FROM topics t
ORDER BY created_at DESC
LIMIT 5