double right join query

Question

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)

year	code	import
2018	390107	0.00
2018	470101	0.00
2018	470103	0.00
2018	471103	0.00
2018	53010101	0.00
2018	53015101	0.00
2019	390107	10.00
2019	470101	0.00
2019	470103	0.00
2019	471103	0.00
2019	53010101	0.00
2019	53015101	0.00
2020	390107	0.00
2020	470101	0.00
2020	470103	0.00
2020	471103	0.00
2020	53010101	0.00
2020	53015101	0.00
2021	390107	175.00
2021	470101	0.00
2021	470103	0.00
2021	471103	0.00
2021	53010101	0.00
2021	53015101	0.00
2022	390107	102.00
2022	470101	101.00
2022	470103	0.00
2022	471103	0.00
2022	53010101	0.00
2022	53015101	140.00

What does "not working" mean? Your query returns a result set ([db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=53095c752a453381aee267ef41559096)), so why isn't it "working"? — Thom A, Aug 05 '22 at 10:18
It fails only when the declaration happen separately, so that the query won't recognize the variables initialized before. In fact putting the creation statements into different cells of the fiddle will fire the error (which OP may be referring to). This problem won't happen for table creation statements (since they have a side-effect on the database). @Larnu — lemon, Aug 05 '22 at 10:27
That's standard for variables, @lemon , that they persist only in the scope and batch they are created. "Not working" could literally be *anything* here, from Getting an error, getting unexpected results, causing a crash, causes the OP's cat to vomit... I would suggest that the value the IOP has used **table** *variables* to provide sample data is likely irrelevant. If, however, they are misunderstanding the scope of persistence of variables, there are plenty of duplicates out there on that. — Thom A, Aug 05 '22 at 10:32
Please clarify via edits, not comments. Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] — philipxy, Aug 05 '22 at 10:39
When you get a result you don't expect, say what you expect & why you expect it, justified by reference to authoritative documentation. (Debugging fundamental.) Otherwise you're just asking us to rewrite yet another definition & presentation for you to misunderstand, with no idea what your misconceptions are. [mre] [ask] Plus for basic problems those will be faqs you can then characterize specifically enough to (re)search & find. Please use standard punctuation & spelling. Please remove/flag obsolete comments. — philipxy, Aug 05 '22 at 11:04
That is not "minimal" representative data. PS It is usually helpful to sort output on all columns or it is difficult to compare to alternatives. — philipxy, Aug 05 '22 at 11:07

score 0 · Accepted Answer · answered Aug 05 '22 at 10:43

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

double right join query

1 Answers1