Question 20784 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search