CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Revathi
Revathi

Reputation: 3

Joining Master table and two fact tables

Iam facing problem during joining of the data. i have tables as follows

Table1

ID  AMT Product
1   10   P1
2   20   P1
3   30   P2
4   40   P2
5   10   P3

Table2

ID1  AMT1
1   10
1   20
1   30
2   20
3   10

Table3

ID3 AMT3
1   20
1   40
2   50
5   20

Table 1 is mater table and table 2 and 3 are fact tables. After joining master and fact table using below query i got out put Result see attachment. i used following query to join the tables.

SELECT * 
FROM [dbo].[Table_1] a 
left join [dbo].[Table_2] b on a.ID=b.ID1 
Left Join [dbo].[Table_3] c on b.ID1=c.ID2

I want to join all 3 tables,after joining when aggregating AMT1 data at Product Level i need following output

Product Amt1
P1       80
P2       10

Can some one guide me how to do this.

Thanks

Revathi

Upvotes: 0

Views: 199

Answers (1)

mtr.web
mtr.web

Reputation: 1515

After some discussion and another look at the data, I understand what you are looking for and what the problem is. The AMT1 and AMT2 values will be duplicated if there are multiple unique combinations of them. The solution below may not be the most efficient, but it uses a UNION to do each of the joins separately, with a null value for the AMT not included in that part of the query:

SELECT z.Product, SUM(z.AMT1), SUM(z.AMT2) 
FROM (
    SELECT a.Product, b.AMT1, NULL as 'AMT2'
    FROM [dbo].[Table_1] a 
    left join [dbo].[Table_2] b on a.ID=b.ID1
UNION ALL
    SELECT a.Product, NULL as 'AMT1', b.AMT2
    FROM [dbo].[Table_1] a 
    left join [dbo].[Table_3] b on a.ID=b.ID2
) as 'z' 
GROUP BY z.Product;

NOTE: This also corrects a problem in the join logic. The both of the two-column tables need to be joined based on the table including Product because you are grouping by the Product feild, rather than joining Table_2 and Table_3

Upvotes: 0

Related Questions