double right join query

i have this table

declare @table table(year int, code int, import decimal(5,2))
insert into @table values

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

declare @codes table (code int)
insert into @codes values

i tried with somethig like that:

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,
       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 

Leave a Reply