CODEWITHSUNDEEP
sqlsql-serverjoincross-apply
Dinesh Saxena
Dinesh Saxena

Reputation: 71

Join One Table with Other Table for Every Row Type

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.

Upvotes: 0

Views: 84

Answers (3)

kumarvik
kumarvik

Reputation: 19

Try this

SELECT C.Category,A.FinYearVal, ISNULL(B.AMOUNT,0) AS AMOUNT
FROM @TempTableA A
CROSS JOIN (SELECT DISTINCT Category FROM @TempTableB) C
LEFT JOIN @TempTableB B ON B.Category = C.Category AND B.FinYear = A.FinYearVal

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Can you try this?

select t1.category,t1.finyearval,coalesce(t2.amount,0) from
(
select t1.category,t2.finyearval from @TempTableB as t1 inner join @TempTableA as t2 on 1=1
) as t1 left join @TempTableB as t2 on t1.finyearval=t2.finyear and t1.Category=t2.Category

Upvotes: 0

Backs
Backs

Reputation: 24913

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

Upvotes: 0

Related Questions