CODEWITHSUNDEEP
mysqljoinmany-to-many
Dani Grosu
Dani Grosu

Reputation: 554

Many to many join

I have this

database diagram

and I am trying to select all users that are friends with a user given by username. I tried something like this

select friends.id_friend
from friends inner join users on users.id =  friends.id_user 
where users.Username = 'Dani'

but I want access to the fields from users table.

Upvotes: 1

Views: 123

Answers (2)

Alex
Alex

Reputation: 17289

SELECT friends.ID_friend, u.*
FROM friends 
INNER JOIN users 
ON users.ID =  friends.ID_User 
LEFT JOIN users u
ON u.ID =  friends.ID_friend 
WHERE users.Username = 'Dani'

Upvotes: 0

TDavis
TDavis

Reputation: 176

Youre going to need to join to the user table twice, like this (change SELECT to fit your needs):

select user.*, friend.*
from friends user_friend_link
inner join users user on user.id =  user_friend_link.id_user 
inner join users friend on friend.id = user_friend_link.id_friend
where user.Username = 'Dani'!

In this case, friends is a linking table between records in the users table. It associates one user record with another user record. To get user record info on both entities in the link table, you have to join it to both linking ids.

Upvotes: 2

Related Questions