Join/Union Query Question

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have been trying to write an SQL query in Access--which
unfortunately does not support full outer joins, although I think the
problem goes beyond that--that has me stumped. I am trying to combine
two tables, and I think the problem can be best described with an
example.

Table 1
TypeID NameInId AmountIn
A Mary 30
A Ed 40
B Bob 50
C Frank 25
C Joe 30

Table 2
TypeID NameOutIdAmountOut
A Mary 40
A Joe 25
B Bob 60
C Frank 50
C Betty 30

Desired Result Table
TypeID NameId AmountIn AmountOut
A Mary 30 40
A Ed 40
B Bob 50 60
C Frank 25 50
C Joe 30
A Joe 25
C Betty 30

Any thoughts or hints would be appreciated.

--Dan
 
First use a union query to assemble a complete list of TypeID & NameInId --
SELECT TypeID, NameId
FROM [Table 1]
UNION SELECT TypeID, NameId
FROM [Table 2];

Name it TypeID_NameID. Then a left join query ---

SELECT TypeID_NameID.TypeID, TypeID_NameID.NameId, AmountIn, AmountOut
FROM (TypeID_NameID LEFT JOIN [Table 1] ON TypeID_NameID.TypeID = [Table
1].TypeID AND TypeID_NameID.NameId = [Table 1].NameId) TypeID_NameID LEFT
JOIN [Table 2] ON TypeID_NameID.TypeID = [Table 2].TypeID AND
TypeID_NameID.NameId = [Table 2].NameId;
 
I have been trying to write an SQL query in Access--which
unfortunately does not support full outer joins, although I think the
problem goes beyond that--that has me stumped.  I am trying to combine
two tables, and I think the problem can be best described with an
example.

Table 1
TypeID  NameInId        AmountIn
A               Mary            30
A               Ed              40
B               Bob             50
C               Frank   25
C               Joe             30

Table 2
TypeID  NameOutIdAmountOut
A               Mary            40
A               Joe             25
B               Bob             60
C               Frank   50
C               Betty           30

Desired Result Table
TypeID  NameId  AmountIn        AmountOut
A               Mary            30              40
A               Ed              40
B               Bob             50              60
C               Frank   25              50
C               Joe             30
A               Joe                            25
C               Betty                           30

Any thoughts or hints would be appreciated.

--Dan
Click to expand...

Please consider:

SELECT A.TypeID, A.NameInID as NameID, A.AmountIn, B.AmountOut
from Table1 as A left join Table2 as B
on ( A.TypeID = B.TypeID ) and ( A.NameInID = B.NameOutID )
UNION
SELECT B.TypeID, B.NameOutID, null, B.AmountOut
from Table2
where not exists
( SELECT 'true'
from Table1
where TypeID = B.TypeID
and NameInID = B.NameOutID )
 
Back
Top