0

I'm trying to do a full outer join of 3 tables and went off the logic of doing a two-table FOJ from here

So instead of doing a UNION on 3 queries (like I would with two tables), I made 7 queries to do a union of. I'm sure that's really inefficient, but it's my first time using Access.

Basically, I have a table with information about Actual Dollars spent. With specifications on what they were spent on (a number ID), who provided them (a number code), and who received them (a different number code). The other two tables have similar specifications, but one shows what is forecasted to be spent, and the other what was originally budgeted to be spent.

I was able to do a FOJ of two tables and get accurate numbers, but I'm having trouble doing a three-way FOJ.

There are cases where in the Actual Table, for a particular ID, Provider Code, and Receiver Code, there may be 3 records of dollars spent, while in the Forecast table, there may be 2, and in the Budget Table, 1. Essentially there's no guarantee that for each combination of Number ID, provider code, receiver code there are the same number of records in the three tables.

To make sure a record wouldn't multiply I made a query of the 3 tables individually and did the Group By function. And the 7 queries I made were the following:

Table 1: Actual inner join w/ Forecast & A inner join w/ B

Table 2: F inner join w/ B & F left outer join with A (Where A is Null)

Table 3: F inner join w/ A & F left outer join w/ B (Where B is Null)

Table 4: B inner join w/ A & B left outer join w/ F (Where F is Null)

Table 5: A left outer join w/ B (Where B is Null) & A left outer join w/ F (where F is null)

Table 6: B left outer join w/ A (Where A is Null) & B left outer join w/ F (where F is null)

Table 7: F left outer join w/ B (Where B is Null) & F left outer join w/ A (where A is null)

Could someone walk me through how to do a 3-Table full outer join? Is there any combination I'm missing? Unfortunately I can't post the tables or give too much specification because I'm not allowed to. I also am not able to alter the tables themselves, I basically only have read-only authorization.

user3783314
  • 21
  • 1
  • 6

1 Answers1

0

You can use union and sequences of left outer joins. I think 3 will do, but you might need 6. Here is a sketch of the SQL:

select *
from A left outer join B left outer join C
union
select *
from B left outer join C left outer join A
union
select *
from C left outer join A left outer join B;

You might need to expand this, depending on the conditions:

select *
from A left outer join B left outer join C
union
select *
from B left outer join C left outer join A
union
select *
from C left outer join A left outer join B
union
select *
from A left outer join C left outer join B
union
select *
from B left outer join A left outer join C
union
select *
from C left outer join B left outer join A;

However, both of these seem like really bad ideas. Instead, just create a temporary table with the ids from all three tables:

select id into ids
from A
union 
select id
from b
union
select id
from c;

Then use a left outer join:

select *
from ids left join A left join B left join C;

Or, better yet. Upgrade to a database such as SQL Server Express (also free) that supports more powerful SQL functionality.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Just to clarify, in the design view of MS Access A left outer join B left outer join C would look like this: A --> B --> C ? Unfortunately another limitation is that there are no IDs on the tables, and I can't add them. I'll look into SQL Server Express, does that have Full Outer Join functionality? – user3783314 Jun 30 '14 at 15:48
  • @user3783314 . . . It not only supports `full outer join`, but you can also use `union`s in subqueries and it supports CTEs. The latter can also be helpful for this type of query. – Gordon Linoff Jun 30 '14 at 15:49
  • Also, why do you say they're bad ideas? I mean obviously there are better ways to do what I'm doing, but taking into account the limitations I'm facing, I just wanted to get a sense of how good/bad of a solution it is to do what you suggested (if I can only use Access) – user3783314 Jun 30 '14 at 16:17
  • @user3783314 . . . These formulations are doing lots of unnecessary work (hence "bad idea"). They are to work around very cumbersome limitations in MS Access, which is further from standard SQL than any other database that I know of. Better to use a database that does a better job of supporting SQL. Of course, if you can't, you can't. – Gordon Linoff Jun 30 '14 at 18:39