Joining 3 tables referencing table 1 and combining values with comma delimiter in MYSQL

Question

I'm having trouble on joining three tables. First attempt on joining two tables is success, but on third table the results are not correct...

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

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  |

Table: 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 try joining the two table with this 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;

and the result are:

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

which is correct, but when i try on third table it duplicates it's value.

this 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;

and the result are:

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

What should i do?

Those `machine_id`, `speed` and `twine` column names in your query can't be found in your example tables. — FanoFN, Feb 16 '23 at 09:06

score 2 · Accepted Answer · answered Feb 16 '23 at 09:00

If you don't group, you'll see there are two rows associated with MN-2. So if you group_concat it'll display the value for the selected column from 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're going to need to use nested selects here. Something like 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 after thought you could also try GROUP_CONCAT with DISTINCT

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;

It works... i am new to sqLanguage, i didn't know that it was possible to nest a select command. Any ways thanks again... — alfredo anojan, Feb 16 '23 at 09:16

score 1 · Answer 2 · answered Feb 16 '23 at 09:07

Looks like your join is producing duplicate rows.

We can achieve the desired output by making use of 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 you the expected output :

machine_number	machine_speed	production_line
MN-1	20	mono
MN-2	20, 25	mono, poly

Joining 3 tables referencing table 1 and combining values with comma delimiter in MYSQL

2 Answers2