MySQL查询-连接问题 [英] MySQL Query - Join Issues

问题描述

我有以下查询，该查询多次重复返回所有相同的行.

I have the following query which is returning all the same rows multiple times repeated.

我需要连接2个表:注释和项目对象，但是无论我尝试哪种方式，这些行都会重复多次.

I need to Join 2 tables: comments and project objects, but no matter which way I try it, the rows are repeating multiple times.

这是当前查询:

SELECT 
acx_comments.created_on AS 'Time',
acx_project_objects.created_by_id AS `Created By`,  
acx_comments.body AS `Comment`,
concat("http://www.example.com/projects/" , acx_project_objects.project_id , "/tasks/" , acx_project_objects.integer_field_1 ) AS URL
FROM acx_comments, acx_project_objects
LEFT JOIN acx_comments v
ON v.parent_id = acx_project_objects.id 
WHERE acx_comments.id > 1500 AND acx_comments.id <= 1575
ORDER BY acx_comments.created_on DESC

当前结果如下:

时间/创建者/评论/URL

Time / Created By / Comment / URL

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

15:35:34/1/测试评论X/URLx

15:35:34 / 1 / Test Comment X / URLx

如您所见-它多次打印相同的评论.

As you can see - it is printing the same Comment multiple times.

我知道这是由于acx_comments.parent_id连接到acx_project_objects.id时有很多重复的事实-许多注释具有相同的父ID，因为每个父可以具有多个注释...但我只想按日期顺序打印一次注释.

I know this is due to the fact that the acx_comments.parent_id has many duplicates when it connects to the acx_project_objects.id - there are many comments which have the same parent id since each parent can have more than one comment... but I just want to print the comments one time, in order by date.

我无法弄清楚用于使其仅打印每个注释一次的正确Join方法.上面的代码没有给我按日期排序的评论列表(仅限唯一).

I can't figure out the correct Join method to use to make it only print each comment once. The code above is not giving me the list of comments ordered by Date (unique only).

由于我已经花了数小时而无法找到解决方案，因此任何建议都将不胜感激.

Any advice would be appreciated as I've spent hours on this and can't figure out the solution.

谢谢！

顺便说一句-我必须使用项目对象表，因为它是构造URL地址所必需的(请参阅concat函数).如果您认为我应该删除该表-这是不可能的

BTW - I have to use the project objects table because it's required to construct the URL address (see concat function). In case you are thinking I should just remove that table - it's not possible

推荐答案

FROM acx_comments, acx_project_objects
LEFT JOIN acx_comments v
ON v.parent_id = acx_project_objects.id 

从技术上讲，您是两次针对acx_comments加入.您的WHERE子句不包含任何JOIN条件，并且ON子句中的JOIN条件仅适用于您的LEFT JOIN中指定的"acx_comments v".

You are technically joining against acx_comments twice. Your WHERE clause does not contain any JOIN conditions, and the JOIN conditions in your ON clause only apply to the "acx_comments v" specified in your LEFT JOIN.

我对正在发生的事情的猜测是，您正在acx_comments和acx_project_objects之间创建笛卡尔乘积，因为它们在WHERE中使用逗号分隔，而没有JOIN条件.然后，您再次加入了对acx_comments的混乱.

My guess as to what is happening, is that you are creating a Cartesian Product between acx_comments and acx_project_objects, because they are comma-separated without JOIN conditions in the WHERE. Then you are JOINing that mess against acx_comments again.

重新编写您的FROM和LEFT JOIN，以便每个表仅列出一次，然后试一试.

Re-write your FROM and LEFT JOIN so that each table is only listed once, and give it a shot.

SELECT 
v.created_on AS 'Time',
o.created_by_id AS `Created By`,  
v.body AS `Comment`,
concat("http://www.example.com/projects/" , o.project_id , "/tasks/" , o.integer_field_1 ) AS URL
FROM acx_comments v
LEFT JOIN acx_project_objects o
ON v.parent_id = o.id 
WHERE v.id > 1500 AND v.id <= 1575
ORDER BY v.created_on DESC

这篇关于MySQL查询-连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！