CODEWITHSUNDEEP
mysql
Marc Hoover
Marc Hoover

Reputation: 97

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?

Upvotes: 0

Views: 31

Answers (2)

Chris Forrence
Chris Forrence

Reputation: 10104

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           |
+----+--------------+----------+---------------+

Upvotes: 1

Dhruvam Gupta
Dhruvam Gupta

Reputation: 502

The following query will do what you want by joining the table with itself.

select t2.display_name from table_name t1 join table_name t2 on t1.referral=t2.id

where table_name is your table's name.

Upvotes: 0

Related Questions