Joining multiple Tables and using Count with MySQL

Question

So I've got myself in a massive confused mess.

Basically, I have a little social network app and I have 4 different tables I need to combine into one view.

Table 1 - User posts
Table 2 - Users
Table 3 - Likes
Table 4 - Comments

I then need to return a list of posts, with the user details and then add columns for the number of likes and number of comments for each post respectively.

If a post doesn't have any likes or comments, then ideally we should show a Zero.

The query below joins everything up, but returns multiple rows of everything, as it is returning 1 row for each comment or like as well.

Anyone able to help me combine these together?

SELECT *
FROM app_posts AS p
LEFT JOIN app_comments AS c ON c.post_id = p.post_id
LEFT JOIN app_user AS u ON u.user_id = p.user_id
LEFT JOIN app_likes AS l ON l.post_id = p.post_id
WHERE u.user_banned = 0
AND p.post_public = 1
ORDER BY p.post_date DESC

Any help would be greatly appreciated!

Table columns are as follows;

app_likes

like_id
post_id
user_id
liked_date

app_comments

comment_id
comment_user_id
post_id
comment_body
comment_date

app_posts

post_id
user_id
post_content
post_date
post_public

app_user

user_id
user_first
user_last
user_avatar
user_banned

An example of what is returned currently is as follows (chopped down for easiness)

You'll see the post_id is repeated multiple times.

What I want it to return is the post_id just once, and with the count of 'likes' and 'comments' in new columns (I don't know how to do this).

Simon

What is your current result and what is your desire result? That will help us to see where is the problem. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 9, 2016 at 14:10
I've just edited my question with a snippet of my current output. Basically I need to have 1 instance of each post_id returned and have a count of 'likes' and 'comments' as new columns added automatically. — Simon Hume
– Simon Hume, Commented Mar 9, 2016 at 14:15
@SimonHume The basic should be GROUP BY but looks like you have repeated data, duplicated comment and dates so you may want to take a look to your query — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 9, 2016 at 14:19

Community · Accepted Answer · 2017-05-23 12:23:47Z

0

You might be missing GROUP BY...

SELECT p.*,u.*,count(distinct c.comment_id),count(distinct l.like_id)
FROM app_posts AS p
LEFT JOIN app_comments AS c ON c.post_id = p.post_id
LEFT JOIN app_user AS u ON u.user_id = p.user_id
LEFT JOIN app_likes AS l ON l.post_id = p.post_id
WHERE u.user_banned = 0
AND p.post_public = 1
GROUP BY p.post_id
ORDER BY p.post_date DESC

Note that MySQL lets you be sloppy with GROUP BY like this, but a lot of other databases would require you to break out the "p.*" into explicit MAX(p.post_id),MAX(p.post_content), etc.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 9, 2016 at 14:16

david

1,0166 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juan Carlos Oropeza Over a year ago

Just because MySQL let you be sloopy doesnt mean you should be sloopy ;).

Juan Carlos Oropeza · Accepted Answer · 2016-03-09 14:17:21Z

0

SELECT p.post_id, COUNT(c.post_id) as num_comments, COUNT(l.like_id) as num_likes
FROM app_posts AS p
LEFT JOIN app_comments AS c ON c.post_id = p.post_id
LEFT JOIN app_user AS u ON u.user_id = p.user_id
LEFT JOIN app_likes AS l ON l.post_id = p.post_id
WHERE u.user_banned = 0
AND p.post_public = 1
GROUP BY  p.post_id
ORDER BY p.post_date DESC

answered Mar 9, 2016 at 14:17

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

Han Arantes · Accepted Answer · 2016-03-09 14:17:22Z

0

Try to add Group by at the end of your query, like this

SELECT *
FROM app_posts AS p
LEFT JOIN app_comments AS c ON c.post_id = p.post_id
LEFT JOIN app_user AS u ON u.user_id = p.user_id
LEFT JOIN app_likes AS l ON l.post_id = p.post_id
WHERE u.user_banned = 0
AND p.post_public = 1
GROUP BY p.post_id
ORDER BY p.post_date DESC

answered Mar 9, 2016 at 14:17

Han Arantes

7751 gold badge7 silver badges20 bronze badges

1 Comment

Juan Carlos Oropeza Over a year ago

GROUP BY without aggregated function?

Collectives™ on Stack Overflow

Joining multiple Tables and using Count with MySQL

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related