Joins In MySQL for fetching multiple tables data

Question

enter image description here

I have two table user and follow. I want to write view such that it will fetch all details of particular user along with that two extra column as follower count and followee count alias.

eg. user id=11 then all details from user tables plus followcount 1 and followed count1

Why have you included all databases ? Include the one which you are using. — analyticalpicasso
– analyticalpicasso, Commented Feb 21, 2014 at 6:28
@agnes i am not including multiple db i want to fetch data from multiple tables — Swapnil
– Swapnil, Commented Feb 21, 2014 at 6:29
I am talking about your tags which you applied to your question — analyticalpicasso
– analyticalpicasso, Commented Feb 21, 2014 at 6:30

Hawk · Accepted Answer · 2014-02-21 08:24:05Z

2

SELECT u.id, 
       u.userid, 
       u.name, 
       u.mobile, 
       (SELECT Count(*) 
        FROM   follow f 
        WHERE  f.followerid = u.userid) AS follower, 
       (SELECT Count(*) 
        FROM   follow f 
        WHERE  f.followeeid = u.userid) AS followee 
FROM   users u

edited Feb 21, 2014 at 8:24

Hawk

5,17014 gold badges55 silver badges78 bronze badges

answered Feb 21, 2014 at 6:54

slavoo

6,11464 gold badges40 silver badges41 bronze badges

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

Comments

analyticalpicasso · Accepted Answer · 2014-02-21 06:53:10Z

0

You can achieve this is by using JOIN statements in your query:

example of how you can achieve your final result:

CREATE VIEW [Followers] AS 
SELECT a.name, a.email, a.mobile, COUNT(SELECT COUNT(followerID) FROM follow WHERE followerID = a.userid), COUNT(SELECT COUNT(followeeID) FROM follow WHERE followeeID = a.userid) FROM users a INNER JOIN follow b ON b.followerID = a.userid

edited Feb 21, 2014 at 6:53

answered Feb 21, 2014 at 6:36

analyticalpicasso

1,9938 gold badges26 silver badges45 bronze badges

1 Comment

Swapnil Over a year ago

above solution gives onlu users details but i need count of users follower and followee in same query

Collectives™ on Stack Overflow

Joins In MySQL for fetching multiple tables data

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related