CODEWITHSUNDEEP
mysqljoin
FireFoxII
FireFoxII

Reputation: 828

mysql LEFT JOIN on row number

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

Upvotes: 0

Views: 241

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7960

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

Upvotes: 1

Related Questions