正在返回两个左连接重复的mysql结果

问题描述：

我想确定用户是否喜欢一个帖子，但我得到重复的结果返回...我错过了什么或有没有更好的（更有效的）方法检索这些数据？正在返回两个左连接重复的mysql结果

SELECT 
p.*, 
u.firstname, 
u.lastname, 
l.post_id AS liked 
FROM `posts` p 
LEFT JOIN `users` u ON u.id = p.author_id 
LEFT JOIN `likes` l ON l.user_id = p.author_id 
WHERE p.author_id=1 AND p.published = 1 
ORDER BY p.post_date DESC LIMIT 0, 10

MySQL表：

**users table** 
id, firstname, lastname 

**posts table** 
id, author_id, post, published, post_date 

**likes table** 
id, post_id, user_id, timestamp

这里是输出：

id, author_id, post, firstname, lastname, liked 
"36","1","sdfsdf","John","Smith","36" 
"36","1","sdfsdf","John","Smith","4" 
"27","1","xcvxvc","John","Smith","36" 
"27","1","xcvxvc","John","Smith","4" 
"29","1","sdfsdf","John","Smith","36" 
"29","1","sdfsdf","John","Smith","4" 
"3","1",""fdgdfg","John","Smith","36" 
"3","1",""fdgdf","John","Smith","4" 
"28","1","data","John","Smith","36" 
"28","1","data","John","Smith","4"

有什么想法？

你选择所有的用户的喜好，基本上是这样。可能你需要在where子句中使用'posts.id =？'来限制该特定的帖子。 – 2015-02-06 16:49:10

我试图抓住10个最近发布的帖子......可以这样做吗？ – Paul 2015-02-06 16:50:05

'l.post_id'可能有帮助。但如果显示实际结果数据（混淆个人数据）会更容易。 – Rufinus 2015-02-06 16:53:11

答

我不知道你可以使用条件语句的连接，因此我最终这样做是为了解决我的问题：

SELECT 
p.*, 
u.firstname, 
u.lastname, 
l.post_id AS liked 
FROM `posts` p 
LEFT JOIN `users` u ON u.id = p.author_id 
LEFT JOIN `likes` l ON l.user_id = p.author_id 
    AND l.post_id = p.id 
WHERE p.author_id=1 
AND p.published = 1 
ORDER BY p.post_date DESC LIMIT 0, 10

答

你必须定义posts之间的许多一对多关系的典型配置和users。但是，有一种特殊的方法可以将这些表连接在一起，使用交叉表likes，而您并不完全了解这些表。

一个交集表包含两个外键，一个到两个相互关联的表中的每一个。在likes的情况下，这是分别参考users表和posts表的user_id和post_id字段。要正确加入这些表，likes使用user_id字段和posts表使用post_id字段加入users表。

表格的顺序对于解决关系 - 并不重要。在你的情况下，因为你想要列出所有的帖子，即使没有与它们相关的“喜欢”，那么posts表首先。然而，从来没有，交叉表可以持续。它必须在两个实体表之前或在它们之间的列表中。每个实体表都连接到相交表，而不是彼此。

像这样：

SELECT p.*, u.firstname + ' ' + u.lastname AS LikedBy 
FROM `posts` p 
LEFT JOIN `likes` l 
    ON l.post_id = p.id 
LEFT JOIN `users` u 
    ON u.id = l.user_id 
WHERE ...

正在返回两个左连接重复的mysql结果

相关推荐