CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 1309

Combine two tables and change user id to user name

I have a problem (first problem is that i have a hard time understand how SQL joins work ;)

But i have two tables, one with user_id´s and user_names, and than i have a table with "connection", users can follow each other.

And i want to change the query so i get usernames from that second table, with connection.

Here is a SQLfiddle http://sqlfiddle.com/#!9/1779d/4

And I'm been trying with something like this:

SELECT users_followers.user_id,
  usernames.username 
  FROM users_followers
JOIN users usernames ON users_followers.follower_id = usernames.id

But with no luck.

so SELECT user_id, follower_id from users_followers WHERE follower_id = 1 gives me 

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
|       3 |           1 |
|       4 |           1 |
|       5 |           1 |
+---------+-------------+

But i want

+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| steve   | demo        |
| adam    | demo        |
| frank   | demo        |
+---------+-------------+

Help is much appreciated!

Upvotes: 0

Views: 149

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You need to join user table twice as

select
u1.user_name as user_name,
u2.user_name as follower_name
from users_followers uf
join users u1 on u1.user_id = uf.user_id
join users u2 on u2.user_id = uf.follower_id

Upvotes: 2

Related Questions