MySQL join multiple columns from same table

Question

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.

Mark Byers · Accepted Answer · 2010-09-17 20:48:43Z

3

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

answered Sep 17, 2010 at 20:48

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Comments

Mike Axiak · Accepted Answer · 2010-09-17 20:47:54Z

0

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

answered Sep 17, 2010 at 20:47

Mike Axiak

12.1k2 gold badges36 silver badges49 bronze badges

Comments

Jeff Day · Accepted Answer · 2010-09-17 22:27:23Z

0

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.

answered Sep 17, 2010 at 22:27

Jeff Day

3,7171 gold badge20 silver badges12 bronze badges

2 Comments

BenTheDesigner Over a year ago

I get syntax errors with this query, Jeff. I believe I did something similar earlier and it returned the same value for both leads and follows - which is incorrect.

Jeff Day Over a year ago

Yeah, you would, that was a dumb mistake. But using the same SUMs that Mike uses above instead of using counts like I stupidly did should work.

Collectives™ on Stack Overflow

MySQL join multiple columns from same table

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related