MySQL join multiple columns from same table

Question

Hi all

I am trying to write a query that returns a users profile information, along with a count of occurrences of the user's ID in 2 columns from another table. An example below:

TableA
userID  userName
1       UserA
2       UserB

TableB
LinkID  leadID  followID
1       1       2
2       1       3
3       2       1

Querying against UserID 1 I would expect to retrieve UserA, 2 (occurences of 1 in leadID), and 1 (occurences of 1 in followID). Any help with this is much appreciated, and thanks in advance.

Answer 1

SELECT a.userName,
       SUM(IF(b.leadId = a.userId, 1, 0) as Leads,
       SUM(IF(b.followId = a.userId, 1, 0) as Follows
FROM TableA a
     TableB b
GROUP BY a.userName

Answer 2

You don't actually need to join for this - you can instead make three separate selects.

SELECT
(
    SELECT userName
    FROM TableA
    WHERE userID = 1
) AS userName,
(
    SELECT COUNT(*)
    FROM TableB
    WHERE leadID = 1
) AS count_leadID,
(
    SELECT COUNT(*)
    FROM TableB
    WHERE followID = 1
) AS count_followID

Result:

userName  count_leadID  count_followID
UserA     2             1             

Answer 3

SELECT a.userName,
       b1.count(*) as leads,
       b2.count(*) as follows
FROM TableA a
INNER JOIN TableB b1 on a.userID = b1.leadID
INNER JOIN TableB b2 on a.userID = b2.followID
GROUP BY a.userName

Depending on how mySQL optimizes and if you have an index on leadID and followID then this could speed up the query, especially if you're going to query just a few users rather than them all at the same time.