MYSQL Joining 3 tables by sequence

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');

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');

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');

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

and the result like this, that not i expected

---

But, im expecting the return of values like below.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.

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