Mysql – Why this join provide multiple value

distinctjoin;MySQLmysql-8.0

This is my Query:

SELECT GROUP_CONCAT(c.categoryName) AS categories
FROM post p
         INNER JOIN join_post_cat jpc USING (postId)
         INNER JOIN categories c USING (categoryId)
WHERE p.postId = 1;

This is Output:

+-----------------------------------------------------+
| categories                                          |
+-----------------------------------------------------+
| Apartment/Flat,Auditorium,Bachelor,Community Center |
+-----------------------------------------------------+
1 row in set (0.00 sec)

This is another Query:

SELECT GROUP_CONCAT(a.areaName) AS locations
FROM join_area ja
         INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE ja.areaId = 29;

This is Output:

+----------------------------------------------------------------+
| locations                                                      |
+----------------------------------------------------------------+
| Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Those two queries data are related with each others by ONE TO MANY relationship. So when i marge both two queries in one Query, those Output got repeated.

This is updated Query:

SELECT GROUP_CONCAT(c.categoryName) AS categories,
       GROUP_CONCAT(a.areaName)     AS locations
FROM post p
         INNER JOIN join_post_cat jpc USING (postId)
         INNER JOIN categories c USING (categoryId)
         INNER JOIN join_area ja USING (areaId)
         INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE p.postId = 1;

This is Output:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| categories                                                                                                                                                                                                                                                          | locations                                                                                                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Apartment/Flat,Apartment/Flat,Apartment/Flat,Apartment/Flat,Apartment/Flat,Auditorium,Auditorium,Auditorium,Auditorium,Auditorium,Bachelor,Bachelor,Bachelor,Bachelor,Bachelor,Community Center,Community Center,Community Center,Community Center,Community Center | Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari,Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

When i add distinct inside GROUP_CONCAT i got the desired output.

This is the Query with Correct Output:

SELECT GROUP_CONCAT(distinct c.categoryName) AS categories,
       GROUP_CONCAT(distinct a.areaName)     AS locations
FROM post p
         INNER JOIN join_post_cat jpc USING (postId)
         INNER JOIN categories c USING (categoryId)
         INNER JOIN join_area ja USING (areaId)
         INNER JOIN area a ON ja.belongAreaId = a.areaId
WHERE p.postId = 1;

Output:

+-----------------------------------------------------+----------------------------------------------------------------+
| categories                                          | locations                                                      |
+-----------------------------------------------------+----------------------------------------------------------------+
| Apartment/Flat,Auditorium,Bachelor,Community Center | Dhaka Division,Dhaka Zhila,Dhaka City,Jatrabari,West Jatrabari |
+-----------------------------------------------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Now i want to know:

  • Why MySQL get same data multiple times?
  • Why do i need to add distinct? (I know to remove duplicate data but why MySQL get those data multiple time)
  • Is there any better way to do this task without getting duplicate data or without using distinct or group?

Those Tables Diagram:

enter image description here

Best Answer

Think of it this way...

Step 1. Build all combinations of rows requested by the JOINs.
Step 2. Do the GROUP BY.

What to do instead?

Yeah, you could do a DISTINCT. But that only hides the problem that the query is doing too much work.

Instead, separate the GROUP BYs:

SELECT ( SELECT GROUP_CONCAT(c.categoryName)
              FROM categories WHERE c.categoryId = p.categoryId
       ) AS categories,
       ( SELECT GROUP_CONCAT(a.areaName)
              FROM join_area WHERE ja.areaId = p.areaId  -- (or whatever)
       )      AS locations
FROM post p
WHERE p.postId = 1;

OK, having subqueries costs something, but at least you are getting the de-dupped answer without taking extra effort to de-dup.

Be sure to follow the tips on many:many tables: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Related Question