CODEWITHSUNDEEP
mysqldatabasejoin
Sunil Kumar Jha
Sunil Kumar Jha

Reputation: 881

SQL Joins. Efficient Query

DB Tables

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.

Upvotes: 0

Views: 33

Answers (1)

Shadow
Shadow

Reputation: 34232

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.

Upvotes: 1

Related Questions