CODEWITHSUNDEEP
mysqlsqlselect
saga
saga

Reputation: 25

Using one table instead of two

I have a problem to display some information. My teacher asked me to change my DB, and it has a big impact on my app.

I have a table in MySql, with columns "user_id", "lastname", and "recruiter_id". The recruiter_id corresponds to the user_id of the recruiter. For example:

user_id  lastname  recruiter_id
1        DO        NULL
2        SMITH     1
3        ROBERT    1

In MySql, I would like to show the name of the person who is the recruiter. For example, for user_id = 3 I would like to show the name "DO", because the recruiter_id of "robert" is 1, and 1 corresponds to the user_id of "DO".

Before my teacher asked me this, I had 2 different tables, and it was much easier. Now I don't know how to proceed. How do I accomplish this?

Upvotes: 1

Views: 78

Answers (2)

Mureinik
Mureinik

Reputation: 311163

This calls for a self-join:

SELECT    a.lastname AS name, b.last_name as recuirter_name
FROM      users a
LEFT JOIN users b ON a.recruiter_id = b.user_id

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can easily do this using a left self join:

select u.*,
    r.lastname as recruiter_name
from your_table u
left join your_table r on u.recruiter_id = r.user_id;

Upvotes: 1

Related Questions