CODEWITHSUNDEEP
mysqljoinleft-join
Daniel C
Daniel C

Reputation: 627

MySQL Query - Join Issues

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

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

Here is the current query:

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

The current result is like this:

Time / Created By / Comment / URL

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

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

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

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

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

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

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

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

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.

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.

Thank you!

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

Upvotes: 1

Views: 508

Answers (2)

Aaron
Aaron

Reputation: 57808

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

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.

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.

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

Upvotes: 2

Aiias
Aiias

Reputation: 4748

You could try using MySQL's GROUP BY on the comment id.

SELECT 
  acx_comments.created_on 'Time',
  acx_project_objects.created_by_id 'Created By',  
  acx_comments.body 'Comment',
  concat("http://www.example.com/projects/" , acx_project_objects.project_id , "/tasks/" , acx_project_objects.integer_field_1 ) '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
GROUP BY acx_comments.id
ORDER BY acx_comments.created_on DESC

Upvotes: 1

Related Questions