Solved

Joining 2 data sets into 1

Posted on 2008-10-10
4
191 Views
Last Modified: 2013-11-29
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.



0
Comment
Question by:bhieb
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22687614
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]
0
 

Author Comment

by:bhieb
ID: 22687721
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.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22688034
try if this will work for you

SELECT Table1.Type, Table1.Parts, Table2.Labor
FROM Table1 LEFT JOIN Table2 ON Table1.Type = Table2.Type
Union
SELECT Table2.Type, Table1.Parts, Table2.Labor
FROM Table2 LEFT JOIN Table1 ON Table2.Type = Table1.Type;
0
 

Author Comment

by:bhieb
ID: 22688436
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question