我有3个表:
用户
id
username
图像
id
user_id
image
user_follow
id
user_id
follow_id
我试着做一个查询,显示我所有的朋友和我朋友的朋友。我需要通过他们的id(从表"users")获得他们的用户名
我的问题是:
$sql = "SELECT f.user_id, username, i.image, i.date FROM users u
INNER JOIN user_follow f on (u.id = f.follow_id)
LEFT OUTER JOIN images i on (f.follow_id = i.user_id)
WHERE
f.user_id = 3 OR f.user_id IN
(
select f2.follow_id
from user_follow f2
where f2.user_id = 3
)
";
它得到了这个:
[0] => Array
(
[user_id] => 6
[username] => 7777
[image] =>
[date] =>
)
[1] => Array
(
[user_id] => 6
[username] => 8888
[image] => 86o99fyyocscfvbzlco0_380.jpg
[date] => 2012-01-23 15:03:26
)
[2] => Array
(
[user_id] => 3
[username] => 6666
[image] => my.jpg
[date] => 2012-01-23 16:40:28
)
[user_id]=>6="users"表中的名称为6666[user_id]=>7="users"表中的名称为7777
这个查询显示除了user_idname之外的所有好的东西。我需要在查询中进行更改,它还将显示user_id(来自"user_follow"表)->username(位于"users table"中)
使用user_id
添加另一个join
和users
,并显示匹配行中的username
?
更新:
SELECT f.user_id, fn.username, i.image, i.date
FROM
users u
INNER JOIN user_follow f on (u.id = f.follow_id)
LEFT OUTER JOIN images i on (f.follow_id = i.user_id)
JOIN users AS fn ON (f.user_id=fn.id)
WHERE
f.user_id = 3 OR f.user_id IN
(
select f2.follow_id
from user_follow f2
where f2.user_id = 3
)