sql join query issues

Question

Im making a friends list which JOINs 2 tables, the FRIENDS table, and the PLAYERS table.

friends table

 player   target     status
 -----------------------------
 john     eric       invited
 roger    moore      friends
 stan     winston    friends

players table

 name      pic     
 -------------------
 john     lol.jpg
 stan     skinhead.gif

Now when on a players page, I need to loop through the friends table and find out how many friends they have. So for this i'd have to check BOTH the player field, and the target field that = $name

Then to get the pic id have to JOIN that with the players table.

This is my query so far, which doesnt work obviously (bit of psuedo):

SELECT p.pic, p.name FROM friends f INNER JOIN players p ON (player OR target)=p.name WHERE (f.player='" .$name. "' OR f.target='" .$name. "') AND status='friends'

$name is the name of the players profile you are on.

Can anyone tell me how to tweak this to make it work?

Answer 1

SELECT p.pic, p.name FROM friends f INNER JOIN players p ON f.name=p.name WHERE (f.player='" .$name. "' OR f.target='" .$name. "') AND status='friends'

friends = f.player in your query ?

Answer 2

Try this:

SELECT p.pic, 
       p.name 
FROM friends f 
   INNER JOIN players p ON p.name IN (f.player, f.target)
WHERE (f.player='" .$name. "' OR f.target='" .$name. "') 
AND status='friends'

which is a shorthand for

SELECT p.pic, 
       p.name 
FROM friends f 
   INNER JOIN players p ON p.name = f.player OR p.name = f.target
WHERE (f.player='" .$name. "' OR f.target='" .$name. "') 
AND status='friends'

Answer 3

You'll need to use a union to get it all in one query.

SELECT * FROM friends JOIN players ON (target = name AND status = friends)
WHERE player = ?
UNION ALL
SELECT * FROM friends JOIN players ON (player = name AND status = friends)
WHERE target = ?