MySql query on two table

Question

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

@Hamish yes I have, from inner join to left join and even tried doing subqueries but still. I am not really good in complex queries like this but the need of it has come — David Addoteye
– David Addoteye, Commented Jan 13, 2015 at 22:34
@Jay Blanchard I have tried a lot so i really don't know which of them will be good enough for you to improve — David Addoteye
– David Addoteye, Commented Jan 13, 2015 at 22:34

mopo922 · Accepted Answer · 2015-01-13 23:11:45Z

2

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.

edited Jan 13, 2015 at 23:11

answered Jan 13, 2015 at 22:14

mopo922

6,3813 gold badges30 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Addoteye Over a year ago

I tried your answer, but it is returning 6 results instead of three

mopo922 Over a year ago

@DavidAddoteye Do you want to exclude results for Jerem, since he is FG3? If so, just add AND users.usercode != 'FG3' to the end of this query.

David Addoteye Over a year ago

Ok, thanks. I have added it and it has remove 2 of the Jerem leaving one. I now have 4 results instead of three. I am wondering why that is left though i have verified the data type and the string

mopo922 Over a year ago

@DavidAddoteye Possibly order of operations? See how I've arranged the WHERE-AND clause now, with parentheses.

Collectives™ on Stack Overflow

MySql query on two table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related