double right join query

August 5, 2022

i have this table

declare @table table(year int, code int, import decimal(5,2))
insert into @table values
(2019,390107,10.00),
(2021,390107,175.00),
(2022,390107,102.00),
(2022,470101,101.00),
(2022,53015101,140.00)

i want to make a query that returns the import for each year and for each code contained in the following tables (return import = 0 where there is no record for a specific combination of year and code):

declare @years table (year int)
insert into @years values
(2018),
(2019),
(2020),
(2021),
(2022)

declare @codes table (code int)
insert into @codes values
(390107),
(470101),
(470103),
(471103),
(53010101),
(53015101)

i tried with somethig like that:

select 
    y.year,
    c.code,
    isnull(t.import,0)
from @table t
right join @years y on t.year = y.year
right join @codes c on t.code = c.code

the query does not return errors (is not a problem of using create Vs declare, nor a a problem with the tables name). but i don’t get the result expected:

Expected Results

Having 6 codes and 5 years I expect 30 records (one for each combination of year and code) along with the corresponding import value from "@table" for that year/code combination (or 0 if the combination is not found)

>Solution :

You need to cross join the Codes and Years to get every combination and then use that in the outer join

SELECT Y.year,
       C.code,
       ISNULL(T.import, 0) AS import
FROM   @years Y
       CROSS JOIN @codes C
       LEFT JOIN @table T
              ON T.year = Y.year
                 AND T.code = C.code