How to get data from 3 tables with same user id?

Question

I have 3 tables like so

Table 1: UserInfo

user_id   userName

123       userOne

Table 2: Post

user_id   postContent

123       This is test message

Table 3: LikePost

user_id   likesPostId

123       This is test message

I would like to run a query to get total number of post likes, posts, and user information from those 3 tables.

I can do this for each one such as in Post table:

SELECT COUNT(*) FROM Post WHERE Post.user_id = '123'

and SELECT * FROM UserInfo WHERE UserInfo.user_id = '123'

Is anyone have better solution in just 1 query? Thank you so much!

https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — Troy Turley, Mar 27 '18 at 11:47

score 1 · Answer 1 · answered Mar 27 '18 at 11:47

Use a structured query (with subqueries) something like this.

 SELECT u.user_id, u.userName, p.num postcount, l.num likecount
   FROM UserInfo u
   LEFT JOIN (
                 SELECT COUNT(*) num, 
                        user_id
                   FROM Post
                  GROUP BY user_id
        ) p ON u.user_id = p.user_id
   LEFT JOIN (
                 SELECT COUNT(*) num, 
                        user_id
                   FROM LikePost
                  GROUP BY user_id
        ) l ON u.user_id = l.user_id

What's going on here? The two subqueries, for example

                 SELECT COUNT(*) num, 
                        user_id
                   FROM LikePost
                  GROUP BY user_id

each generate a virtual table with either zero or one row per user_id, showing a count for each user_id. You then join those virtual tables to your UserInfo table.

Use LEFT JOIN because ordinary innner JOIN will suppress users that lack either posts or likes.

Thank you for your reply but where should I enter user Id such as:'123" or '333' — KevinVuD, Mar 27 '18 at 12:47
I also I test the code and get this error: unknown column user_id in field list — KevinVuD, Mar 27 '18 at 13:07

Faraz · Accepted Answer · 2018-03-27T12:45:42.727

0

Try This

SELECT ui.userName,Count(p.*),
Count(lp.*) as TotalPostLikes
 FROM UserInfo ui 
INNER JOIN Post p on p.user_id=ui.user_id
INNER JOIN LikePost lp on lp.user_id=ui.user_id
WHERE ui.user_id = '123'
GROUP BY ui.userName

If you want to select Username, Post and Likes on post, try the following

 SELECT ui.userName,p.postContent as PostContent,
        (SELECT COUNT(lp.user_id) FROM LikePost lp 
    WHERE lp.user_id=ui.user_id) as Likes,

(SELECT COUNT(_p .user_id) FROM Post _p 
    WHERE _p .user_id=ui.user_id) as TotalPosts

     FROM UserInfo ui 
    INNER JOIN Post p on p.user_id=ui.user_id
    WHERE ui.user_id = '123'

edited Mar 27 '18 at 12:45

answered Mar 27 '18 at 11:46

Faraz

644
4
13

Unfortunately this approach counts the product of posts and likes. – O. Jones Mar 27 '18 at 11:48
@O. Jones , Yes, if you need to select Username, Post of current User and Likes of current user the try 2nd query – Faraz Mar 27 '18 at 11:55
Sorry for any confusion. I just tried your answer but it is missing number of post count – KevinVuD Mar 27 '18 at 12:39
Updated for Number of Posts – Faraz Mar 27 '18 at 12:46
Thanks for an updated but the code just return 1 for post and 1 for likes even thought I think there are more than just 1 for both. – KevinVuD Mar 27 '18 at 13:01
NVM I think your code shows correct counts but instead of just 1 row which shows user info, like count, post count but it shows multiple rows with unique post content but same like count and post count amounts for each row. Any suggestions? – KevinVuD Mar 27 '18 at 13:28
I remove inner join and p.post content and problem solved. I will do some more tests and let you know soon. – KevinVuD Mar 27 '18 at 13:31
@KevinVuD Thats great. – Faraz Mar 27 '18 at 13:45

score 0 · Answer 3 · edited Mar 27 '18 at 12:13

0

Yes you can do it within one query using leftjoin on Post and LikePost like below

SELECT COUNT(*),User.userName FROM UserInfo as User
leftjoin  Post as Post on Post.user_id = User.user_id
leftjoin  LikePost as LikePost on LikePost.user_id = User.user_id      
where Post.user_id = 123
group by Post.user_id

edited Mar 27 '18 at 12:13

Muhammad Omer Aslam

21,325
8
39
62

answered Mar 27 '18 at 11:52

Mathan Kumar

899
9
18

Thank you for your reply but the code just return count(*) and user name. I hope it will show like count, post count column with user info as well. – KevinVuD Mar 27 '18 at 13:13

How to get data from 3 tables with same user id?

3 Answers3