php MySql QUERY for Friends relations Table help

Question

EDIT by:lawrence.

I got the right query now

select * from users, friends where (users.id=friends.user_id1 and friends.user_id2=$profileID) or (users.id=friends.user_id2 and friends.user_id1=$profileID)

Question answered

I need some help joining results from my friends and users table

This is what my friends table look like

id     user_id1     user_id2   
1   |  2         |  3  
1   |  2         |  4  
1   |  2         |  5  
1   |  6         |  2  

Users table

id    name  
2  |  sarah  
3  |  emma  
4  |  lawrence  
5  |  cynthia  
6  |  suzie  

I could easily just have two rows for each relation and do a simple query.
But i prefer having one row per relation,

So lets assume that we are watching page member.php?profile=2
and there is a list of friends, what does the query look like.

This works fine if i have two rows per relation but i dont want that....

SELECT * FROM friends, users WHERE friends.user_id1 = $profileID AND friends.user_id2 = users.id ORDER BY friends.id DESC LIMIT 16

Do you get me? something along like

SELECT * FROM friends,users WHERE friends.user_id1 = $profileID AND ALSO WHERE friends.user_id2 = $profileID AND THEN GET FROM users WHERE users.id = friends.user_id1 AND ALSO WHERE users.id = friends.user_id2  

I hope I made myself clear

Answer 1

I'm not sure i understand your question but won't this do?

SELECT * FROM friends, users where friends.user_id1 = $profileID or friends.userid2 = $profileID and users.id = friends.user_id1 or  users.id = friends.user_id2

Answer 2

You want a left join (using the LEFT JOIN operator), not a cartesian join (using the FROM table1, table2 syntax).

http://www.w3schools.com/sql/sql_join_left.asp

Tip: With your cross-reference table instead of having an id column you can create a compound key.