CODEWITHSUNDEEP
mysqlsqljoin
stack
stack

Reputation: 10228

How can I get the name of friends?

I have two tables like these:

// users
+----+----------+
| id |   name   | 
+----+----------+
| 1  | John     |
| 2  | Peter    |
| 3  | Martin   |
| 4  | Barman   |
| 5  | Jack     |
| 6  | Ali      |
+----+----------+

// friends
+-----------+---------+
| friend_id | user_id |
+-----------+---------+
| 2         | 1       |
| 5         | 1       |
| 1         | 3       |
| 2         | 3       |
| 5         | 3       |
| 2         | 4       |
| 1         | 5       |
| 4         | 5       |
+-----------+---------+

And this is expected result:

+-----------+---------+
| f_name    | u_name  |
+-----------+---------+
| Peter     | John    |
| Jack      | John    |
| John      | Martin  |
| Peter     | Martin  |
| jack      | Martin  |
| Peter     | Barman  |
| John      | Jack    |
| Barman    | Jack    |
+-----------+---------+

I know, I need to use JOIN clause. But I don't know how should I do that correctly?

SELECT * FROM friends f
INNER JOIN users u
ON f.friend_id = u.id

Do I need another JOIN?

Edited: Can I get them in the same column and remove the duplicates? Like this: (I don't care about the order)

+-----------+
| f_name    |
+-----------+
| Peter     |
| Jack      |
| John      |
| Martin    |
| Barman    |
+-----------+

Actually I want a name list of everybody which exits into friend table (either friend_id column or user_id column)

Upvotes: 0

Views: 46

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can try the following

SELECT u1.name AS f_name, u2.name AS u_name
FROM friends
    INNER JOIN users u1 ON friends.friend_id = u1.id 
    INNER JOIN users u2 ON friends.user_id = u2.id

To get the names of all users used in friends you can use the following:

SELECT DISTINCT name 
FROM users WHERE users.id IN (
    SELECT friend_id FROM friends 
    UNION 
    SELECT user_id FROM friends
)

Upvotes: 2

Related Questions