Join 3 tables referencing table 1 in MYSQL and combine values ​​with comma separator

Question

I have a problem joining three tables. The first attempt to join two tables is successful, but the result is incorrect on the third table...

I have three tables machine_list is mainTable, and then applicable_rpm and applicable_product are some details of machine_list information

Table:machine_list

| id | machine_number | machine_brand |
---------------------------------------
| 1  |     MN-1       |     TOYO      |
| 2  |     MN-2       |    AMITA      |

Table:applicable_rpm

| id | mc_recordID | rpm |
--------------------------
| 1  |      1      | 20  |
| 2  |      2      | 20  |
| 3  |      2      | 25  |

Form:applicable_product

| id | mc_recordID | productline|
---------------------------------
| 1  |      1      |    mono    |
| 2  |      2      |    mono    |
| 3  |      2      |    poly    |

I want to return like this:

| machine_number |   rpm  |   twine    |
----------------------------------------
|      MN-1      | 20     | mono       |
|      MN-2      | 20, 25 | mono, poly |

I first tried to join the two tables using the following query:

SELECT t1.machine_number, GROUP_CONCAT(' ', t2.speed) machine_speed
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
GROUP BY t1.id;

turn out:

| machine_number |  rpm   |
---------------------------
|      MN-1      |  20    |
|      MN-2      | 20, 25 |

This is correct, but when I try the third table, it duplicates its values.

This is my query:

SELECT t1.machine_id,
       GROUP_CONCAT(' ', t2.speed) machine_speed,
       GROUP_CONCAT(' ', t3.twine) production_line
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id;

turn out:

| machine_number |  rpm   |   twine    |
----------------------------------------
|      MN-1      | 20, 20 | mono, poly |
|      MN-2      | 20, 25 | mono, poly |

what should I do?

Answer 1

It looks like your join is generating duplicate rows.

We can achieve the desired output by using subqueries.

SELECT t1.machine_number, t2.machine_speed, t3.production_line
FROM machine_list t1
LEFT JOIN (
  SELECT mc_recordID, GROUP_CONCAT(' ', speed) AS machine_speed
  FROM applicable_rpm
  GROUP BY mc_recordID
) t2 ON t1.id = t2.mc_recordID
LEFT JOIN (
  SELECT mc_recordID, GROUP_CONCAT(' ', twine) AS production_line
  FROM applicable_product
  GROUP BY mc_recordID
) t3 ON t1.id = t3.mc_recordID;

This will return the output you expected:

machinary code	Machine speed	production line
MN-1	20	Mono
MN-2	20, 25	Single crystal, polycrystalline

Answer 2

If you do not group, you will see that there are two rows associated with MN-2. So if you do a group_concat it will display the values ​​of the selected column in both rows.

SELECT *
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID;

You will need to use nested selections here. Something similar to the following:

SELECT machine_number, 
    (SELECT GROUP_CONCAT(rpm) FROM applicable_rpm WHERE mc_recordID = t1.ID) as rpm,
    (SELECT GROUP_CONCAT(productline) FROM applicable_product WHERE mc_recordID = t1.ID) as twin,
    FROM machine_list t1;

As an afterthought, you could also try using DISTINCT for GROUP_CONCAT

SELECT t1.machine_id,
       GROUP_CONCAT(DISTINCT t2.speed) machine_speed,
       GROUP_CONCAT(DISTINCT t3.twine) production_line
FROM machine_list t1
INNER JOIN applicable_rpm t2 ON t1.id = t2.mc_recordID
INNER JOIN applicable_product t3 ON t1.id = t3.mc_recordID
GROUP BY t1.id;