MySQL inner join problem

Question

I have 2 tables :

users : contains id and name
friends : contains id1 and id2

Let's suppose I have this data in the users table :

id : 1 , name : FinalDestiny
id : 2 , name : George

And this data in the friends table:

id1: 1 , id2: 2

So this means that 1 is friend with 2.

I need with one single query to say that id1 is friend with id2( but I need the names) Like: FinalDestiny is friend with George. I have to make one mysql inner or left join but I don't know how

Answer 1

Select F1.Name,
       F2.Name
From Friends F
INNER JOIN USER F1 ON F.ID1 = F1.id
INNER JOIN USER F2 ON F.ID2 = F2.id

Explanation:

As friend is composition of two user, friend1 and friend2, so we will try to join friend with 2 user, 1 user table corresponding to ID1 and other to ID2 getting information for both the friends from respective user table.

Answer 2

I don't know whether you can do it. But this is the closest that I could get it for you.

select u1.name, u2.name from users u1, users u2, friends f where u1.id = f.id1 and u2.id = f.id2

Answer 3

use aliases and double-join the user table with the friends table:

    SELECT `u1`.`name` `name1`, `u2`.`name` `name2`
      FROM `friends` `f`
INNER JOIN `users` `u1`
        ON `f`.`id1` = `u1`.`id`
INNER JOIN `users` `u2`
        ON `f`.`id2` = `u2`.`id`

Answer 4

You'll need to join on your users table twice.

SELECT 
   u1.name AS FirstPerson
  ,u2.name AS SecondPerson 
FROM 
  friends f
INNER JOIN
  users u1
ON
  u1.id = f.id1
INNER JOIN
  users u2
ON
  f.id2 = u2.id

Answer 5

You should be able to query this without the explicit use of joins using something along the lines of...

SELECT * FROM (friends, users AS u1, users AS u2) WHERE
friends.id1=u1.id AND friends.id2=u2.id

Answer 6

You need to select from friends and join the users table twice (for each friend)

SELECT u1.Name, u2.Name 
FROM Friends as fr 
  INNER JOIN users as u1 on fr.Id1 = u1.id
  INNER JOIN users as u2 on fr.Id2 = u2.id