mysql - 需要在一个 MySql 查询中包含用户、帖子和评论的所有信息

Question

这是我的表格:

评论:

# id, user_id, post_id, message, date
'1', '1', '1', 'This is a comment bla bla bla', '2016-09-04 11:36:11'
'2', '1', '1', 'This is another comment bla bla bla', '2016-09-04 11:39:59'
'3', '1', '2', 'This is a first comment', '2016-09-04 16:35:43'
'4', '2', '2', 'Relax my friend', '2016-09-04 16:35:43'
'5', '1', '2', 'Ok, cool.', '2016-09-04 16:36:03'
'6', '3', '1', 'Cool bro', '2016-09-12 17:51:24'
'7', '3', '2', 'OMG! :O', '2016-09-12 17:51:53'

用户:

# id, password, username, photo, background_photo, email, first_name, last_name
'1', 'rqwerr23r2', 'welkdic', 'img/Friends/guy-2.jpg', './../../img/profile-cover.jpg', 'test@test', 'Omarion', 'welkdic'
'2', '23rqw3rq32', 'McBrewk', 'img/Friends/woman-1.jpg', './../../img/profile-cover.jpg', 'test2@test', 'Hillary', 'McBrewk'
'3', 'wer23r23', 'jmorr', 'img/Friends/guy-2.jpg', './../../img/profile-cover.jpg', 'test3@test', 'John', 'Morrison'

和帖子:

# id, user_id, date, type, message, image, shares, likes, comments_count
'1', '1', '2016-09-01 12:09:00', 'uploaded a photo', 'TODAY IS..... beatifull day.', 'img/Post/game.jpg', '0', '0', '2'
'2', '2', '2016-09-04 16:22:11', 'made a post', 'bla bla bla \r\n                     for bootstrap css hmtl js framework.', '', '0', '0', '3'

这是我的查询:

SELECT *
        FROM posts AS p
        RIGHT JOIN users AS u ON u.id = p.user_id
        RIGHT JOIN comments AS c ON p.id = c.post_id AND u.id = c.user_id
        RIGHT JOIN users AS up ON up.id = c.user_id;

我需要在一个查询中获得有关帖子用户和评论的所有信息。现在，在我的结果中，一切看起来都很好，但有一个问题，即帖子表的重复结果(以蓝色标记 - 位置 nr 2)。我不知道这是为什么。 HOW IT LOOKS RIGHT NOW

但它应该类似于下一行位置 nr3 且具有 NULL 字段: SHOULD LOOKS LIKE THAT已经尝试过“group by”和“distinct”但没有运气:(我需要结果中的所有评论用户和帖子

Answer 1

你应该试试这个

SELECT
    *
FROM
    posts p
INNER JOIN
    users u
ON
    u.user_id=p.user_id
LEFT JOIN
    comments c
ON
    p.post_id=c.post_id
INNER JOIN
    users uu
ON
    c.user_id=uu.user_id