CODEWITHSUNDEEP
sqljoin
bartek
bartek

Reputation:

SQL JOIN: how would a JOIN look like if I want to get user2's information from table "users"?

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

Upvotes: 0

Views: 291

Answers (3)

Spencer Ruport
Spencer Ruport

Reputation: 35117

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

Upvotes: 1

John MacIntyre
John MacIntyre

Reputation: 13031

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 ...'

?

Upvotes: 1

Deniss Kozlovs
Deniss Kozlovs

Reputation: 4841

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

Upvotes: 3

Related Questions