CODEWITHSUNDEEP
mysqljoin
Swapnil
Swapnil

Reputation: 654

Joins In MySQL for fetching multiple tables data

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

Upvotes: 2

Views: 578

Answers (2)

slavoo
slavoo

Reputation: 6076

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

Upvotes: 2

analyticalpicasso
analyticalpicasso

Reputation: 1993

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

Upvotes: 0

Related Questions