MySql query on three tables

Question

Help needed, I have three table "USERS", "FRIENDS" and "STATUS" i am having difficulty joining them by condition in the "FRIENDS TABLE"

 users table
id | name | usercode
--------------------
1 | david | 2WM
2 | Samme | E5N
3 | Awudu | C0Q
4 | John  | VX6
5 | Jerem | FG3

Friends Table
id | actor | target
--------------------
1  | E5N   | FG3
2  | 2WM   | VX6
3  | FG3   | 2WM
4  | C0Q   | VX6
5  | FG3   | VX6

 Status Table
id | usercode | status
--------------------
1  | E5N      | I am busy now
2  | 2WM      | The night is falling too fast
3  | FG3      | Good day
4  | C0Q      | Very tired, trust me
5  | VX6      | YeLLLLOOO

What i will like to get is all "users" from "USERS TABLE" and their corresponding "status" from the "STATUS TABLE" who either has 'FG3' as "target" or 'FG3' as "actor" from the "FRIENDS TABLE"

So the results will be like

id | name  | usercode | actor | target | status
------------------------------------------------
 2 | Samme | E5N      | E5N   | FG3    | I am busy now
 1 | david | 2WM      | FG3   | 2WM    | The night is falling too fast
 5 | John  | VX6      | FG3   | VX6    | YeLLLLOOO

Please any help will be much appreciated, thank you.

Answer 1

SELECT u.name,
       u.usercode,
       f.actor,
       f.target,
       s.status
FROM Users u,
    Friends f,
    Status s 
WHERE ((u.usercode = f.actor AND 'FG3' = f.target)
OR (u.usercode = f.target AND 'FG3' = f.actor)) 
AND s.usercode = u.usercode

Answer 2

First, join your user table with the friends table, then, join the status table with the friends table

SELECT friend_user.name,
       friend_user.usercode,
       Friends.actor,
       Friends.target,
       friend_status.status
FROM users AS my_user_table
JOIN Friends ON Friends.actor = my_user_table.usercode
JOIN users AS friend_user ON friend_user.usercode = Friends.target
OR friend_user.usercode = Friends.actor
JOIN Status AS friend_status ON friend_status.usercode = friend_user.usercode
WHERE my_user_table.usercode = 'FG3'