Get relationship 2 tables deep in MySQL

Question

I have the following tables:

• projects (id)
• items (project_id)
• comments (item_id)

I want to:

1. count all the items in a project
2. count all the comments in a project.

For #1, I am doing the following:

SELECT 
    p.*,
    (SELECT COUNT(*) FROM items WHERE project_id=p.id) items_count,
FROM projects p

How can I go one level deeper and count the comments?

Answer 1

Assuming that id is the primary key of projects and both items and comments have primary keys, say id, you can do a LEFT join of projects to the other tables and aggregate:

SELECT p.*,
       COUNT(DISTINCT i.id) items_count,
       COUNT(c.id) comments_count
FROM projects p
LEFT JOIN items i ON i.project_id = p.id
LEFT JOIN comments c ON c.item_id = i.id
GROUP BY p.id;

See a simplified demo.