noa-dev noa-dev - 5 months ago 10
SQL Question

mysql search with two joins and a count

I am currently trying to write a general query which returns the content of 1 table and another joined table plus the count of resulting rows from a third table.

Now my description might seem abstract so I'll try to visualize it

Tables:

posts
| ID | title | description | creator_id |
1 Title1 Descr1 1
2 Title2 Descr2 1

users
| ID | name | avatar |
1 User1 PATH

interactions
| ID | type | target_id | identifier |
1 view 1 IP
2 view 1 IP


Now what I am looking for is an output like this:

| ID | title | description | name | avatar | view_count |
1 Title1 Descr1 User1 PATH 2
2 Title2 Descr2 User1 PATH 0


My current query looks like following:

SELECT
posts.id, posts.title, posts.description,
users.name, users.avatar,
COUNT(interactions.id) AS view_count
FROM
posts
LEFT JOIN
users
ON
posts.creator_id = users.id
LEFT JOIN
interactions
ON
posts.id = interactions.target_id


But only prints out the posts result which has an interaction like this:

| ID | title | description | name | avatar | view_count |
1 Title1 Descr1 User1 PATH 2


How do I need to alter the query in order to also get the other rows which happen to not have any interactions yet?

Thank you for your help!

Answer

You can simply subquery third table to count entries:

SELECT 
    posts.id, posts.title, posts.description,
    users.name, users.avatar,
    (SELECT COUNT(*) FROM interactions i WHERE i.target_id = posts.id) AS view_count
FROM
    posts
LEFT JOIN
    users
    ON
        posts.creator_id = users.id

This is also better for performance (no groups, no unoptimized joins)

Comments