Selecting from three tables

Question

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.

I got the solution to the above but am trying something else with the solution provided which I explained in the comment area of the solution provided.

SELECT 
t.id,
t.title,
t.body,
t.poster,
t.created_at,
s.supporter,
IFNULL((SELECT COUNT(*) FROM comments c WHERE c.id = t.id), 0) AS comcount,
IFNULL((SELECT COUNT(*) FROM supports s WHERE s.topic_id = t.id), 0) AS     supcount,
CASE WHEN (s.supporter  = "Davies Alex") THEN '1'  ELSE '0' END sup,
CASE WHEN (c.commenter  = "Davies Alex") THEN '1'  ELSE '0' END com
FROM topics t, comments c, supports s
ORDER BY created_at DESC

Answer 1

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.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

Update for new requirement:

SELECT 
    t.id,
    t.title,
    t.body,
    t.poster,
    t.created_at,
    s.supporter,
    IFNULL(COUNT(c.id), 0) AS comcount,
    IFNULL(COUNT(s.id), 0) AS supcount,
    SUM(IF(s.supporter IS NOT NULL AND s.supporter  = "Davies Alex", 1, 0)) > 0 AS sup,
    SUM(IF(c.commenter IS NOT NULL AND c.commenter  = "Davies Alex", 1, 0)) > 0 AS com
FROM topics t
LEFT JOIN comments c ON c.id = t.id
LEFT JOIN supports s ON s.topic_id = t.id
GROUP BY t.id
ORDER BY created_at DESC

Answer 2

In your query, you require list.id to either match comments.id or supports.topic_id. If you use an outer join, you'll be able to retrieve data from the initial table even though the joined tables don't match or contain any data.

SELECT
    topics.id, topics.title, topics.body, topics.poster, list.created_at,
    count(comments.id) as comcount,
    count(supports.topic_id) as supcount
FROM lists
LEFT JOIN comments ON comments.id = topics.id
LEFT JOIN supports ON supports.topic_id = topics.id
ORDER BY created_at DESC
LIMIT 5