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;
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)
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;
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
