SQL Joins and MS Access - How to combine multiple

Question

I have four tables in Access 2010, each with the same primary key. I'd like to join all the data in all four tables into one table with columns for each value tied to the primary key over all the tables. So, for example:

Table1

ID   Value1

1    10       
2    7
3    4
4    12

Table 2

ID   Value2

1    33
2    8
6    19
7    4

Table 3

ID   Value3

1    99
2    99
5    99
7    99

I'd like to create:

Table 4

ID  Value1  Value2  Value3

1   10      33      99
2   7       8       99
3   4
4   12
5           99
6           19      
7           4       99

I'm using MS Access and I know I have to basically use 3 joins (left, right, inner) to get a full join, but I'm not exactly sure about how to structure the query.

Could someone please give me some example SQL code to point me in the right direction as to how to produce this result?

Here is what I have so far. This combines all the tables, but it looks like I'm still missing some data. Have I done something wrong:

SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry LEFT JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) LEFT JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) LEFT JOIN OFSP ON Medicare.cptcode = OFSP.cptcode
UNION
SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry RIGHT JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) RIGHT JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) RIGHT JOIN OFSP ON Medicare.cptcode = OFSP.cptcode
UNION
SELECT Coventry.cptcode, Coventry.[Fee Schedule], CT6002.[Fee Schedule], Medicare.[Fee Schedule], OFSP.[Fee Schedule]
FROM ((Coventry INNER JOIN CT6002 ON Coventry.cptcode = CT6002.cptcode) INNER JOIN Medicare ON CT6002.cptcode = Medicare.cptcode) INNER JOIN OFSP ON Medicare.cptcode = OFSP.cptcode;

Answer 1

How about this?

SELECT id
, max(v1) as value1
, max(v2) as value2
, max(v3) as value3
FROM 
(
    select id
    , value1 as v1
    , iif(true,null,value1) as v2
    , iif(true,null,value1) as v3 
    from Table1

    union 

    select id, null , value2 , null  from Table2

    union 

    select id, null , null , value3 as v3 from Table3
)
group by id
order by id

How it works:

• Rather than doing a join, I put all of the results into one "table" (my sub query), but with value1, value2 and value3 in their own columns, and set to null for the tables that don't have those columns.

• The iif statements in the first query are to say that I want v2 and v3 to be the same data type as v1. It's a dodgy hack but seems to work (sadly access works out the type from the first statement in the query, and casting withclng(null) didn't work). They work because the result of the iif must be of the same type as the last two parameters, and only the last parameter has a type, so that gets inferred from this; whilst the first parameter being true means that the value returned will only ever be the second parameter.

• The outer query then squashes these results down to one line per id; since fields with a value are greater than null and we have at most one field with a value per id, we get that value for that column.

I'm not sure how performance compares with the MS article's way of doing this, but if you're using access I suspect you have other things to worry about ;).

SQL Fiddle: http://sqlfiddle.com/#!6/6f93b/2 (For SQL Server since Access not available, but I've tried to make it as similar as possible)

Answer 2

Create an union of all the ids from all tables, this way you get the id column in table4. Then encapsulate this union in a subselect (ids) and create left joins between this subselect (the parent join) and table1, table2, table3 (the childs join). Then select what you need...

SELECT 
ids.id, 
t1.Value1, 
t2.Value2, 
t3.Value3
FROM ((
(select id from table1 
union 
select id from table2
union 
select id from table3)  AS ids 
LEFT JOIN Table1 AS t1 ON ids.id = t1.ID) 
LEFT JOIN Table2 AS t2 ON ids.id = t2.ID) 
LEFT JOIN Table3 AS t3 ON ids.id = t3.ID;

Answer 3

Here is a variation on JohnLBevan's answer that will work if [Value1], [Value2], and [Value3] are of different types. For example, with

Table1

ID  Value1
--  ------
 1      10
 2       7
 3       4
 4      12

Table2

ID  Value2      
--  ------------
 1  thirty-three
 2  eight       
 6  nineteen    
 7  four        

Table3

ID  Value3    
--  ----------
 1  1999-01-01
 2  1999-01-01
 5  1999-01-01
 7  1999-01-01

the query

SELECT ID, MAX(v1) AS Value1, MAX(v2) AS Value2, MAX(v3) as Value3
FROM (
        SELECT 0 AS ID, 0 AS v1, '' AS v2, #2001-01-01# AS v3
        FROM (SELECT COUNT(*) FROM Table1)
    UNION ALL
        SELECT ID, Value1 AS v1, NULL AS v2, NULL AS v3
        FROM Table1
    UNION ALL
        SELECT ID, NULL AS v1, Value2 AS v2, NULL AS v3
        FROM Table2
    UNION ALL
        SELECT ID, NULL AS v1, NULL AS v2, Value3 AS v3
        FROM Table3
)
WHERE ID > 0
GROUP BY ID

returns

ID  Value1  Value2        Value3    
--  ------  ------------  ----------
 1      10  thirty-three  1999-01-01
 2       7  eight         1999-01-01
 3       4                          
 4      12                          
 5                        1999-01-01
 6          nineteen                
 7          four          1999-01-01

Explanation:

The first piece of the UNION subquery creates a single row with dummy values to ensure that the result of the UNIONs has the correct column types

SELECT 0 AS ID, 0 AS v1, '' AS v2, #2001-01-01# AS v3
FROM (SELECT COUNT(*) FROM Table1)

returns

ID  v1  v2  v3        
--  --  --  ----------
 0   0      2001-01-01

The rest of the UNION subquery appends the actual rows from each table

    SELECT 0 AS ID, 0 AS v1, '' AS v2, #2001-01-01# AS v3
    FROM (SELECT COUNT(*) FROM Table1)
UNION ALL
    SELECT ID, Value1 AS v1, NULL AS v2, NULL AS v3
    FROM Table1
UNION ALL
    SELECT ID, NULL AS v1, Value2 AS v2, NULL AS v3
    FROM Table2
UNION ALL
    SELECT ID, NULL AS v1, NULL AS v2, Value3 AS v3
    FROM Table3

giving us

ID  v1  v2            v3        
--  --  ------------  ----------
 0   0                2001-01-01
 1  10                          
 2   7                          
 3   4                          
 4  12                          
 1      thirty-three            
 2      eight                   
 6      nineteen                
 7      four                    
 1                    1999-01-01
 2                    1999-01-01
 5                    1999-01-01
 7                    1999-01-01

The outer aggregation query excludes the first row with the dummy values (WHERE ID > 0), gives us one row per ID (GROUP BY ID), and uses the MAX() function to return a value if there is one (or Null if there isn't).

ID  Value1  Value2        Value3    
--  ------  ------------  ----------
 1      10  thirty-three  1999-01-01
 2       7  eight         1999-01-01
 3       4                          
 4      12                          
 5                        1999-01-01
 6          nineteen                
 7          four          1999-01-01