CODEWITHSUNDEEP
mysqlsqljoin
user967451
user967451

Reputation:

How to do this JOIN to SELECT from 2 MySQL tables?

I'm very new to joins, so far I've been doing relatively simple joins but this one got me stumped.

I have 2 tables that look like this:

Friends Table

friendship_id | friend_init | friend_accept | status

1             | 18          | 10            | 1
2             | 13          | 18            | 0

Users Table

user_id | email | username | password | kittens, etc...

10      | --    | --       | --       | --  
13      | --    | --       | --       | --
18      | --    | --       | --       | --

If I am trying to select all the info about user_id 18's friends from both tables where the friends record status = 1, what would the join look like?

Upvotes: 0

Views: 78

Answers (3)

Sachin
Sachin

Reputation: 18747

select * 
from user u 
join friends f
on u.user_id = f.friends_accept
where u.user_id = 18 and f.status = 1

union

select * 
from friend f
join user u
on u.user_id = f.friend_status
where u.user_id = 18 and f.status = 1

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Select * 
from users
LEFT join Friends 
  ON Users.User_ID = Friends.Friend_init 
  OR users.User_ID = Friends.Friend_Accept 
Where Friends.Status = 1

LEFT join is used because if a user has no FRIENDS yet; the user will still show up.

Upvotes: 0

Amadan
Amadan

Reputation: 198294

SELECT users.*
FROM users JOIN friends ON friends.friend_accept = users.user_id
WHERE friend.friend_init = 18 AND friend.status = 1
UNION
SELECT users.*
FROM users JOIN friends ON friends.friend_init = users.user_id
WHERE friend.friend_accept = 18 AND friend.status = 1

Drop the UNION... if your database is symmetrical (you have both (18,10) and 10,18), or if you only want to see friendships from one side.

Upvotes: 3

Related Questions