SQL JOIN help

Question

SELECT * FROM friends WHERE (user1 = $userid OR user2 = $userid) AND accepted = 1

how would a JOIN look like if i want to get lets say user2's information from table "users"?

table friends: id int(11) NOT NULL auto_increment, user1 int(11) NOT NULL, user2 int(11) NOT NULL, date datetime NOT NULL, accept tinyint(1) NOT NULL, type varchar(32) NOT NULL

users: id mediumint(8) NOT NULL auto_increment, username varchar(32) NOT NULL, password varchar(40) NOT NULL

Answer 1

SELECT u.*, f.* FROM friends f 
JOIN users u ON u.id=f.user2 
WHERE (f.user1= $userid OR f.user2=$userid) 
AND f.accepted = 1

Answer 2

Something like the following.

SELECT f.*, u1.*, u2.* FROM friends f
INNER JOIN users u1 ON f.user1 = u1.id
INNER JOIN users u2 ON f.user2 = u2.id
WHERE (f.user1 = $userid OR f.user2 = $userid) AND f.accepted = 1

Answer 3

thanks but it doesnt seem to work... i tried both of your codes

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\WAMP\www\profile.php on line 153

$sql = mysql_query(" SELECT u., f. FROM relations f JOIN users u ON u.id=f.user2 WHERE (f.user1=$row[id] OR f.user2=$userid) AND f.accepted = '1' ");

while($friend = mysql_fetch_array($sql)) { php....

Answer 4

You said you unsuccessfully tried (based on based on Mark Tyler's answer?)

$sql = mysql_query(" SELECT u., f. FROM relations f JOIN users u ON u.id=f.user2 WHERE (f.user1=$row[id] OR f.user2=$userid) AND f.accepted = '1' ");

But shouldn't

'select u., f. from ...'

be

'select u.*, f.* from ...'

?