My First Table and its data is :
DECLARE @TempTableA TABLE (FinYearVal VARCHAR(9))
FinYearVal
----------
2007-2008
2008-2009
2009-2010
2010-2011
2011-2012
2012-2013
2013-2014
2014-2015
Then I have another table with data as :
DECLARE @TempTableB TABLE (Category VARCHAR(10), FinYear VARCHAR(9), AMOUNT NUMERIC(18,10))
Category FinYear AMOUNT
---------- ------------- ----------
A 2013-2014 100.0000
A 2014-2015 200.0000
B 2012-2013 100.0000
B 2013-2014 200.0000
B 2014-2015 300.0000
B 2015-2016 400.0000
C 2011-2012 100.0000
C 2012-2013 200.0000
I want my Table1 to be Right Joined with Table2 for Every Category, just as we would separately Right Join and union the Data.
The Expected Result is :
Category FinYearVal AMOUNT
---------- ---------- ----------
A 2007-2008 0.0000
A 2008-2009 0.0000
A 2009-2010 0.0000
A 2010-2011 0.0000
A 2011-2012 0.0000
A 2012-2013 0.0000
A 2013-2014 100.0000
A 2014-2015 200.0000
B 2007-2008 0.0000
B 2008-2009 0.0000
B 2009-2010 0.0000
B 2010-2011 0.0000
B 2011-2012 0.0000
B 2012-2013 100.0000
B 2013-2014 200.0000
B 2014-2015 300.0000
C 2007-2008 0.0000
C 2008-2009 0.0000
C 2009-2010 0.0000
C 2010-2011 0.0000
C 2011-2012 100.0000
C 2012-2013 200.0000
C 2013-2014 0.0000
C 2014-2015 0.0000
NOTE: My Table2 has many Categories where I would dynamically choose how many categories I want to be joined in the Query.
SELECT t.FinYearVal, t.Category, ISNULL(ttb.AMOUNT,0)
FROM (
SELECT tta.FinYearVal, d.Category
FROM @TempTableA tta
CROSS JOIN (SELECT DISTINCT ttb.Category FROM @TempTableB ttb) AS d
) AS t
LEFT OUTER JOIN @TempTableB ttb ON t.FinYearVal = ttb.FinYear AND ttb.Category = t.Category
ORDER BY t.Category, t.FinYearVal
If you have special Category table, query can be impruved by replacing cross join on distinct with cross join on this table