Left join from 2 tables with same ID-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.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 T1.location = T2.location and T1.rownumber = T2.rownumber