MySQL inner join single query
I have 2 tables :
users : contains id and name
friends : contains id1 and id2
Let's suppose I ha开发者_C百科ve 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
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.
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`
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
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
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
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
精彩评论