我正在写一个时间轴PHP脚本:
- 搜索用户之间的所有关系
- 当
relations.friend=posts.user加入 - 加入
users表
posts表当relations.friend = users.id 时，脚本工作正常，并检索用户的朋友发送的所有帖子。
SELECT relations.friend as uid, users.name, users.email, posts.id as pid, posts.text, posts.date
FROM `relations`
INNER JOIN `posts` ON relations.friend = posts.id
INNER JOIN `users` ON relations.friend = users.id
WHERE relations.user = 2
我需要将用户自己的帖子添加到结果中，但是我不能这样做。我的表是这样的:
文章
----------------------------------------------------------
id text user status date
----------------------------------------------------------
1 Post 1 1 1 01-01-2014
2 Post 2 2 1 01-02-2014
3 Post 3 3 1 01-03-2014
4 Post 4 4 1 01-03-2014
5 Post 5 5 1 01-04-2014
--------------------------------------------
id user friend status
--------------------------------------------
1 1 2 1
2 2 1 1
3 2 3 1
4 3 2 1
5 3 1 1
6 4 1 1
7 5 1 1
8 2 4 1
--------------------------------------------
id name email
--------------------------------------------
1 Adam adam@mail.com
2 Bryan bryan@mail.com
3 Mark mark@mail.com
4 Yuri yuri@mail.com
5 Andrew andrew@mail.com
任何想法?
将结果进行合并
SELECT relations.friend as uid, users.name, users.email, posts.id as pid, posts.text, posts.date
FROM `relations`
INNER JOIN `posts` ON relations.friend = posts.id
INNER JOIN `users` ON relations.friend = users.id
WHERE relations.user = 2
UNION
SELECT users.id as uid, users.name, users.email, posts.id as pid, posts.text, posts.date
FROM `users`
INNER JOIN `posts` ON posts.user = users.id
WHERE users.id = 2