MYSQL Joining 3 tables by sequence

All we need is an easy explanation of the problem, so here it is.

I’m gonna try to joining 3 tables by sequence,

Table 1

CREATE TABLE table_1(tb1_id int,rpp_id int);
INSERT INTO table_1(tb1_id,rpp_id) VALUES ('1','127');

tb1_id rpp_id
1 127

Table 2

CREATE TABLE table_2(rpp_id int,sequence int);
INSERT INTO table_2(rpp_id,sequence) VALUES 
('127','1'),
('127','1'),
('127','1'),
('127','1'),
('127','1'),
('127','2'),
('127','3');
rpp_id sequence
127 1
127 1
127 1
127 1
127 1
127 2
127 3

Table 3

CREATE TABLE table_3(tb3_id int,rpp_id int,code_generate VARCHAR(100));
INSERT INTO table_2(tb3_id,rpp_id,sequence) VALUES 
('1','127','PL.461'),
('2','127','PL.428'),
('3','127','PL.319'),
('4','127','PL.306'),
('5','127','PL.301'),
('6','127','PL.292'),
('7','127','PL.291');
tb3_id rpp_id code_generate
1 127 PL.461
2 127 PL.428
3 127 PL.319
4 127 PL.306
5 127 PL.301
6 127 PL.292
7 127 PL.291

I’ve try some query like this

SELECT B.`sequence`, C.`code_generate`
FROM table_1 A
LEFT JOIN table_2 B ON B.`rpp_id`= A.`rpp_id`
LEFT JOIN table_3 C ON C.`rpp_id`=B.`rpp_id`
WHERE B.`rpp_id`=127

sequence code_generate
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
1 PL.292
1 PL.291
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
1 PL.292
1 PL.291
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
1 PL.292
1 PL.291
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
1 PL.292
1 PL.291
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
1 PL.292
1 PL.291
2 PL.461
2 PL.428
2 PL.319
2 PL.306
2 PL.301
2 PL.292
2 PL.291
3 PL.461
3 PL.428
3 PL.319
3 PL.306
3 PL.301
3 PL.292
3 PL.291

and the result like this, that not i expected


But, im expecting the return of values like below.

sequence code_generate
1 PL.461
1 PL.428
1 PL.319
1 PL.306
1 PL.301
2 PL.292
3 PL.291

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Do you need in this:

WITH
cte_2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY rpp_id ORDER BY sequence) rn
    FROM table_2
),
cte_3 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY rpp_id ORDER BY tb3_id) rn
    FROM table_3
)
SELECT cte_2.sequence, cte_3.code_generate
FROM cte_2
JOIN cte_3 USING (rpp_id, rn)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e83b4aaae197143ceb9a73184e2d2095

PS. Some DDL/DML are wrong – fixed.

PPS. table_1 not needed.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply