Marc Hoover Marc Hoover - 3 months ago 9
MySQL Question

Using SELECT to get id, then get another column from another row

I have a table setup like this:

id (INT) | display_name (VARCHAR) | referral (INT)


referral is the id of the referral user. Is there a way that I can pull the referral ID, then use that to get the display_name of the referring member in one call? Or is it easier to do with two separate calls?

Answer

Essentially, all you'd need to do is JOIN your table on itself.

SELECT child.*, parent.display_name AS referral_name 
    FROM users AS child 
        JOIN users AS parent ON child.referral = parent.id
    WHERE child.id = 2

This aliases the users table as child, then joins on the same table (aliased as parent).

+----+--------------+----------+---------------+
| id | display_name | referral | referral_name |
+----+--------------+----------+---------------+
|  2 | Jim          |        1 | Tim           |
+----+--------------+----------+---------------+

If the referral relationship may not exist, then you can use a LEFT JOIN instead:

SELECT child.*, parent.display_name AS referral_name 
    FROM users AS child 
        LEFT JOIN users AS parent ON child.referral = parent.id

+----+--------------+----------+---------------+
| id | display_name | referral | referral_name |
+----+--------------+----------+---------------+
|  1 | Tim          |     NULL | NULL          |
|  2 | Sam          |        1 | Tim           |
|  3 | Kimberly     |        1 | Tim           |
+----+--------------+----------+---------------+