bhieb
asked on
Joining 2 data sets into 1
I have the following senerio.
I have 2 tables that contain the following info.
Table1
Type Parts
A 1
B 2
X 4
Table2
Type Labor
A 4
B 5
C 6
What I need is to output this.
Type Parts Labor
A 1 4
B 2 5
C 0 6
X 4 0
I cannot simply use an outer join becuase there may be Types in one and not the other and vice versa (records C and X in this example). I realize I could just create a Master [Type] list then outer join on it, but the example here is oversimplified since my key is actually 3 fields not just 1 like [Type] here.
I think I need a union query, but I cannot seem to get the following to work.
Select Type, Parts form Table1
union
Select Type, Labor from Table2
This returns Type and just parts no labor.
I have 2 tables that contain the following info.
Table1
Type Parts
A 1
B 2
X 4
Table2
Type Labor
A 4
B 5
C 6
What I need is to output this.
Type Parts Labor
A 1 4
B 2 5
C 0 6
X 4 0
I cannot simply use an outer join becuase there may be Types in one and not the other and vice versa (records C and X in this example). I realize I could just create a Master [Type] list then outer join on it, but the example here is oversimplified since my key is actually 3 fields not just 1 like [Type] here.
I think I need a union query, but I cannot seem to get the following to work.
Select Type, Parts form Table1
union
Select Type, Labor from Table2
This returns Type and just parts no labor.
ASKER
That still only outputs TotParts.
Here is the actually query (there are 4 key fields not just Type).
Select Fleet,SITE, Reason, ReasCd, Desc, sum(LCost) as TotLabor
FROM RO_Labor
Group By Fleet, SITE, Reason, ReasCd, [Desc]
Union
Select Fleet, SITE, Reason, ReasCd, Desc, sum(PCost) as TotPartts
FROM RO_Parts
Group By Fleet, SITE, Reason, ReasCd, [Desc]
This is what it outputs.
Fleet SITE Reason ReasCd Desc TotLabor
QT VV 04 0 Not Applicable 327.2
Not column for Total Parts.
Here is the actually query (there are 4 key fields not just Type).
Select Fleet,SITE, Reason, ReasCd, Desc, sum(LCost) as TotLabor
FROM RO_Labor
Group By Fleet, SITE, Reason, ReasCd, [Desc]
Union
Select Fleet, SITE, Reason, ReasCd, Desc, sum(PCost) as TotPartts
FROM RO_Parts
Group By Fleet, SITE, Reason, ReasCd, [Desc]
This is what it outputs.
Fleet SITE Reason ReasCd Desc TotLabor
QT VV 04 0 Not Applicable 327.2
Not column for Total Parts.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cap that worked (technically), however I have 4 different data sets so combining all possible combinations woould will be a huge pain. I think I will build a master table with all the keys then left join that to the 4 sub tables to get the totals. Was just hoping that I could avoid that.
FROM Table1
GROUP BY [Type]
UNION
SELECT [Type], 0 AS TotParts, Sum(Labor) AS TotLabor
FROM Table2
GROUP BY [Type]