CODEWITHSUNDEEP
sql
ShittyAdvice
ShittyAdvice

Reputation: 265

Select another user from same table

So I have a table with users

A few users are "owners" A user can have a owner

Now I want to select the owner from the user (Both in the same table)

Example: Owner:

user_id: 34
user_name: hiimowner
user_owner_id: NULL

User:

user_id: 63
user_name: randomperson
user_owner_id: 34

Now I'm looking for the proper SQL query to be able to find the user_name of the owner

So if I can request the owner of "randomperson" which should return "hiimowner"

Upvotes: 0

Views: 59

Answers (3)

Mureinik
Mureinik

Reputation: 311348

The trick you're looking for is a self join:

SELECT o.username
FROM   users o
JOIN   users u ON u.user_owner_id = o.user_id
WHERE  u.user_name = 'randomperson'

Upvotes: 3

AK47
AK47

Reputation: 3797

try this,

Select u.*,o.user_name from User as u
left join  User o on o.user_id = u.user_owner_id

If want to filter specific record then add where clause,

Select u.*,o.user_name from User as u
left join  User o on o.user_id = u.user_owner_id
where u.user_id = 63

Upvotes: 3

blue
blue

Reputation: 1949

SELECT * FROM table AS t1
JOIN table AS t2
ON t1.user_id = t2.user_owner_id
WHERE t2.user_id = 63

Upvotes: 2

Related Questions