Link to home
Start Free TrialLog in
Avatar of bhieb
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.



Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

SELECT [Type], Sum(Parts) AS TotParts, 0 AS TotLabor
FROM Table1
GROUP BY [Type]
UNION
SELECT [Type], 0 AS TotParts, Sum(Labor) AS TotLabor
FROM Table2
GROUP BY [Type]
Avatar of bhieb
bhieb

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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bhieb

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.