SQL Joins. Efficient Query

Question

What i need from the above tables are:

1.All user details corresponding to a id.

2.Number of post he had made.

3.Number of followers he has and number of people he is following (followid=user1.user2 [User1 is followin user2].

4.Let's say user1 is visting user2 profile, i need to find out if there exists followid corresponding to user1 and user2 i.e. user1.user2 in follow_detail.

I have achieved 1 and 2 by joining tables (post_detail and user_detail) and 3 by using a separate query (When-then in mysql). I was wondering if i can get all this in one SQL query.This is returned by profile API so hitting DB with multiple queries should not be good. If DB design is not good enough for this to get all above data in one query then i can change it.

Shadow · Accepted Answer · 2017-03-22 16:07:45Z

1

By using subqueries you can combine the results into a single query. Whether that's more efficient than multiple simple queries - I'm not so sure. You need to test it.

select ud.*,
       (select count(*)
        from post_detail where post_detail.user_id=ud.user_id) as post_count,
       (select count(*)
        from follow_detail where follow_detail.following=ud.user_id) as follower_count,
       (select count(*)
        from follow_detail where follow_detail.follow=ud.user.id and follow_detail.following=...) as follower_user 
from user_detail ud
where ud.user_id=...

You need to provide the user ids in place of the .... I took quite a guess with the last subquery, since it was not clear what you would like to see there.

answered Mar 22, 2017 at 16:07

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

2 Comments

Sunil Kumar Jha Over a year ago

From 4 what i want is:Suppose on facebook if i visits your profile, then i should know that if i am following you or not. Let's say my userid=123456 and your's is 234567 then i need to find if in my follow_detail table exits 123456.234567 (a boolean to notify if i am following you or not.)

Sunil Kumar Jha Over a year ago

Thanks to you.I did it (that 4th one).Thanks buddy.

Collectives™ on Stack Overflow

SQL Joins. Efficient Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related