I have two tables Like A and B. Each have one column (ID) in ascending order. I want to join tables LIKE C Table. Datas are not statical but in A and B the row counts are always equal. I’ve tried join statements but couldn’t find out. Thanks for your help.
x
A (ID)
--
2
3
4
6
8
B (ID)
--
11
12
13
14
15
C ( IDA , IDB )
--
2 11
3 12
4 13
6 14
8 15
Advertisement
Answer
If you’re using a database that supports row_number() such as Oracle, postgresql, sql sql server:
select a_id, b_id
from (select row_number() over(order by id) as a_rn, id as a_id from a) x
join (select row_number() over(order by id) as b_rn, id as b_id from b) y
on x.a_rn = y.b_rn
Fiddle: http://sqlfiddle.com/#!15/5ac6b/1/0
If you’re using mysql you can mimic row_number using a variable:
select a_id, b_id
from (select @rn := @rn + 1 as a_rn, id as a_id
from a
cross join (select @rn := 0) r) x
join (select @rx := @rx + 1 as b_rn, id as b_id
from b
cross join (select @rx := 0) r) y
on x.a_rn = y.b_rn