CODEWITHSUNDEEP
phpmysqlsql
David Addoteye
David Addoteye

Reputation: 1641

MySql query on two table

I have two table 'users' and 'friends' I am having difficulty joining them

     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

Basically i want to select all users from USERS table who has 'FG3' in either target or actor column in the FRIENDS table. The result will be 

id | name  | usercode | actor | target
--------------------------------------
 2 | Samme | E5N      | E5N   | FG3
 1 | david | 2WM      | FG3   | 2WM
 5 | John  | VX6      | FG3   | VX6

I have triend everything i know but still i am not getting the correct results I will be glad if anyone can help me since I need to present this work tomorrow morning. Thank you

Upvotes: 0

Views: 51

Answers (1)

mopo922
mopo922

Reputation: 6381

Looks like you want to join on usercode equals actor or target, then put the 'FG3' part in a WHERE clause:

SELECT users.id, users.name, users.usercode, friends.actor, friends.target
FROM users
INNER JOIN friends
    ON users.usercode = friends.actor OR users.usercode = friends.target
WHERE users.usercode != 'FG3'
AND (friends.actor = 'FG3' OR friends.target = 'FG3');

Using INNER JOIN limits your query to only records that exist in both tables.

Upvotes: 2

Related Questions