CODEWITHSUNDEEP
sqlvbams-access
Shad M Khan
Shad M Khan

Reputation: 41

Simulating a FULL OUTER JOIN in Access

I need to join two tables and get the output as follows

T1

S    B   C   P
---  --  --  --
ABC  B1  C1  10
ABC  B2  C2  11
ABC  B3  C3  12

T2

S    B   C   P
---  --  --  --
ABC  B1  C1  12
ABC  B2  C2  13
ABC  B5  C5  14

I need the below output

S    B   C   P1  P2
---  --  --  --  --
ABC  B1  C1  10  12
ABC  B2  C2  11  13
ABC  B3  C3  12  0
ABC  B5  C5  0   14

Please note: I am using Microsoft Access and Access SQL does not directly support FULL OUTER JOIN queries.

Upvotes: 1

Views: 578

Answers (3)

mucio
mucio

Reputation: 7119

SELECT s,
       b,
       c,
       SUM(temp_p1) as p1,
       SUM(temp_p2) as p2
  FROM (
         SELECT t1.s, 
                t1.b, 
                t1.c, 
                t1.p as temp_p1, 
                0 as temp_p2 
           FROM t1
      UNION ALL
         SELECT t2.s, 
                t2.b, 
                t2.c, 
                0 as temp_p1, 
                t2.p as temp_p2 
           FROM t2
       )
GROUP BY s,
         b,
         c

Upvotes: 1

Krzysztof
Krzysztof

Reputation: 16130

You can simulate FULL OUTER JOIN:

SELECT 
    T1.S, T1.B, T1.C, T1.P AS P1, IIF(ISNULL(T2.P), 0, T2.P) AS P2 
FROM
    T1 LEFT JOIN
    T2 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
UNION ALL
SELECT 
    T2.S, T2.B, T2.C, IIF(ISNULL(T1.P), 0, T1.P) AS P1, T2.P AS P2 
FROM
    T2 LEFT JOIN
    T1 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
WHERE T1.S IS NULL

Upvotes: 3

KanimozhiEthiraj
KanimozhiEthiraj

Reputation: 86

SELECT 
    T1.S, T1.B, T1.C, T1.P P1, T2.P P2 
FROM
    T1 left outer JOIN
    T2 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
UNION 
SELECT 
    T2.S, T2.B, T2.C, T1.P P1, T2.P P2 
FROM
    T2 LEFT outer JOIN
    T1 ON (T1.S = T2.S AND T1.B = T2.B AND T1.C = T2.C)
WHERE T1.S IS NULL

Upvotes: 0

Related Questions