获取用户的帖子和/或评论共享博客使用SQL在帖子、用户、博客、SQL
这（我的）SQL的问题会让我发疯，如果不解决这个周末！
This (My)SQL problem will make me crazy if not solved this week-end!
一个简单的共享博客（指许多作者投稿）。让我们考虑在我的数据库中的两个表：
A simple shared blog (means many authors contribute). Let's consider that two tables on my database:
文章
ID AUTHOR_ID 标题 内容评论
ID AUTHOR_ID 的post_id 内容目标：我想显示贡献者的所有活动（=>固定AUTHOR_ID）。我的意思的活动是：
Goal: I want to display all the activities of a contributor (=> fixed author_id). What I mean by activity is:
如果用户创建的帖子和评论：显示文章标题和用户意见 如果用户创建的职位，但不会评论：只显示文章标题 如果用户没有创建的职位，但评论：显示文章标题和用户意见我想这个SQL脚本：
SELECT p.id AS post_id, p.author_id AS post_author_id, c.author_id AS comment_author_id, title, c.content
FROM Posts p JOIN Comments c ON p.id = c.post_id
WHERE p.author_id = $userId
OR c.author_id = $userId
看起来不错，但它并没有给我在哪里，用户创建后的行，但没有发表评论。
Looks fine, but it doesn't give me the row where the user created the post, but doesn't comment.
你知道吗？ 感谢名单提前。
Any idea? Thanx in advance.
推荐答案
要模拟FULL OUTER JOIN在MySQL中，你需要UNION帖子的结果外加入注释，这些注释外的连接到帖子 - 像这样：
To simulate FULL OUTER JOIN in MySQL, you need to UNION the results of Posts outer joined to Comments, with those of Comments outer joined to Posts - like so:
SELECT p.id AS post_id,
p.author_id AS post_author_id,
c.author_id AS comment_author_id,
p.title,
c.content
FROM Posts p
LEFT JOIN Comments c ON p.id = c.post_id AND c.author_id = $userId
WHERE p.author_id = $userId
UNION ALL
SELECT p.id AS post_id,
p.author_id AS post_author_id,
c.author_id AS comment_author_id,
p.title,
c.content
FROM Posts p
RIGHT JOIN Comments c ON p.id = c.post_id
WHERE c.author_id = $userId
