CODEWITHSUNDEEP
mysql
protasm
protasm

Reputation: 1297

MySQL: Selecting Twice from One Table

Apologies for this vague title... I'm having a hard time even summarizing what I'm trying to do. I suspect it's a very basic SQL function but I lack the technical vocabulary for it.

I have a table with three fields: person_id (number), person_name (string), best_friend_id (number).

A couple of sample records might be: (1, "John", 2) and (2, "Mary", 3). These sample records state that Person 1 is John, whose best friend is Person 2; and, that Person 2 is Mary, whose best friend is some other Person with id 3.

Is it possible to write a SELECT statement for a MySQL database that would show both the person's name and the name of the best friend for each record in the table? Such as, (1, "John", 2, "Mary"); (2, "Mary", 3, "Fred"); and so on.

Thank you!

Upvotes: 0

Views: 47

Answers (1)

Anonymous Duck
Anonymous Duck

Reputation: 2978

You can join the table to itself, 

SELECT tbl1.person_id, 
       tbl1.person_name, 
       tbl1.best_friend_id, 
       tbl2.person_name 
FROM table_name tbl1
LEFT JOIN table_name tbl2 ON tbl1.person_id = tbl2.best_friend_id

Upvotes: 1

Related Questions