Left join from 2 tables with same ID

Question

I have tabl1e1 and table2 with data
table1

location    costA
 a           5
 a           10
 a           15
 b           11
 b           12

table2

Location    CostB
 a          100
 b          100

My goal to get the result

location    costA   costB
  a          5       100
  a          10       
  a          15       
  b          11      50
  b          12       

My query

select T1.location, T1.cost
from (
  select location, cost
  , row_number() over ( partition by location order by cost) rownumber
  from table1
) T1 left join (
  select location, cost
  , row_number() over ( partition by location order by cost ) rownumber
  from table2
) T2 on T2.location = T2.cost and T1.rownumber = T2.rownumber

I got

location    costA    missing costB column
 a           5
 a           10
 a           15
 b           11
 b           12  

Not sure why but can you point out the missing one. Thank you.

Answer 1

First of all you are expecting three columuns in result and your select statement contains only 2.

select T1.Location, T1.Cost

2nd the join should be

T2 on T1.[location] = T2.[location] and T1.rownumber = T2.rownumber

Below is the complete working example

DECLARE @table1 as table
(
    [location] char,
    costA int
)
DECLARE @table2 as table
(
    [location] char,
    costB int
)

INSERT INTO @table1
VALUES
 ('a', 5)
,('a', 10)
,('a', 15)
,('b', 11)
,('b', 12)
 
INSERT INTO @table2
VALUES
 ('a', 100)
,('b', 100)

select T1.[location], T1.costA, T2.costB
from (
  select [location], costA 
  , row_number() over ( partition by location order by costA) rownumber
  from @table1
) T1 left join (
  select [location], costB
  , row_number() over ( partition by location order by costB ) rownumber
  from @table2
) T2 on T1.[location] = T2.[location] and T1.rownumber = T2.rownumber

Answer 2

The join

T2 on T2.location = T2.cost and T1.rownumber = T2.rownumber

should be on

 T2 on T1.location = T2.location and T1.rownumber = T2.rownumber

select 
  T1.location, 
  T1.costA,
  T2.costB
from (
  select 
    location, 
    costA,
    row_number() over ( partition by location order by costA) rownumber
  from table1
) T1 
left join (
  select 
    location, 
    costB, 
    row_number() over ( partition by location order by costB ) rownumber
  from table2
) T2 
on T1.location = T2.location and T1.rownumber = T2.rownumber
GO

location | costA | costB
:------- | ----: | ----:
a        |     5 |   100
a        |    10 |  null
a        |    15 |  null
b        |    11 |   100
b        |    12 |  null

db<>fiddle here