mysql LEFT JOIN on row number

Question

I have this two table

*tableA
id - name
1  - a
2  - b

*tableB
col - name
a  - red
b  - green
b  - white
a  - black

and on this query

SELECT a.id
     , a.name
     , b.name 
  FROM tableA a 
  LEFT 
  JOIN tableB b 
    ON A.name = B.id

I have

1 - a - black
1 - a - red
2 - b - green
2 - b - white

now i want to JOIN another tableC getting first 4 element based on position

tableC
pos - name
1 - first
2 - second
3 - third
4 - fourth
5 - fifth

having this result

1 - a - black - first
1 - a - red - second
2 - b - green - third
2 - b - white - fourth

how to JOIN last table with rowNumber -> tableC.pos

this might be what you are looking for https://stackoverflow.com/questions/13566695/select-increment-counter-in-mysql — RisingSun, Aug 08 '19 at 17:48

score 1 · Accepted Answer · answered Aug 08 '19 at 17:49

Try adding a row number to your first query like below and use it while joining to tableC:

DECLARE @row_number int := 0;

select t.*, c.name
from (
       SELECT a.id, a.name, b.name, (@row_number:=@row_number + 1) AS rn 
       FROM tableA a 
       LEFT JOIN tableB b ON A.name = B.col
) t
left join tableC c on c.pos = t.rn

mysql LEFT JOIN on row number

1 Answers1