获取用户的帖子和/或评论共享博客使用SQL在 [英] Get user Posts and/or Comments in a sharing blog using 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

这篇关于获取用户的帖子和/或评论共享博客使用SQL在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！