MySQL join operation on multiple key occurrences

Question

I've got a problem with the join in MySQL application which suppose to be my homework.
Let's say I got following schema:

User {
  id: int,
  name: string,
}

Friend_Pivotal {
  user_rec_id: foreign key on user.id, int
  user_inv_id: foreign key on user.id, int
  status: string,
}

What I like to get:

"Select all users with their friends."

I would like to receive information about User and IDs of users who are his friends. Definition of friend is:

  user_rec_id is the same as user.id or user_inv_id is the same as user.id
  and
  status is 'accepted'

I'm aware it might not be easy to get in one single query, but I'm not that good in MySQL, and pointing me in direction how to do that would be much appreciated.

Answer 1

You can join user and pivotal with this condition:

on u.id = p.user_rec_id or u.id = p.user_inv_id

Live on jsfiddle: http://sqlfiddle.com/#!9/0b313/2/0

select 
  * 
 from 
   myuser as u join friend_pivotal as p on u.id = p.user_rec_id or u.id = p.user_inv_id
 where 
   status = 'accepted';

This will show a list of user joined with his/her friends