how to display user followers on each social media platform use Query SQL ?

This is my code:

SELECT a.name, b.name, b.followers FROM users a JOIN socials b ON a.id = b.user_id

this is users table:

this is socials table:

How to do SQL queries so that it becomes like this below:

CodePudding user response：

Since you are only asking for a simple query, you can achieve it like this, although this isn't a solution that you can expand on, it should serve as a guideline to what you can accomplish using group_concat:

select 
    user.name, 
    group_concat(case when social.name = 'facebook' then social.follower end) as Facebook,
    group_concat(case when social.name = 'twitter' then social.follower end) as Twitter,
    group_concat(case when social.name = 'instagram' then social.follower end) as Instagram
from social
    join user on user.id = social.user_id
group by user.name;

This will create the results based on the tables you showed, the only issue with doing this is, if the user does not have a record in the social table, it will return null error.

CodePudding user response：

i have got the answer, thanks all

SELECT
socials.user_id,
users.name,
COALESCE(SUM(CASE WHEN socials.name = 'fb' THEN followers END), 0) AS facebook,
COALESCE(SUM(CASE WHEN socials.name = 'ig' THEN followers END), 0) AS instagram,
COALESCE(SUM(CASE WHEN socials.name = 'twitter'  THEN followers END), 0) AS twitter
FROM socials
INNER JOIN users ON users.id = socials.user_id
GROUP BY socials.user_id;