CODEWITHSUNDEEP
mysqlsql
KevinVuD
KevinVuD

Reputation: 611

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

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!

Upvotes: 0

Views: 505

Answers (3)

Faraz Babakhel
Faraz Babakhel

Reputation: 664

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'

Upvotes: 0

Mathan Kumar
Mathan Kumar

Reputation: 942

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

Upvotes: 0

O. Jones
O. Jones

Reputation: 108839

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.

Upvotes: 1

Related Questions