Full Outer Join in Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Full Outer Join in Access

Author

Topic

LucasLondon
Starting Member

3 Posts

Posted - 2007-07-28 : 06:48:07

Hi,

Hope someone can help me with this. I'm trying to do a full outer join in Access across 3 Tables using a union query:

BOE
INDICES
TECHNICALS

The common field among them is the date field but the tables differ in terms of the number of rows (dates) they have and also the number of columns/fields. Hence I want to bring back all rows from all three tables even those that are unmatched by the date field. I've been told I need to use a union query to do this but I am having problems.

I ran the query below, but it generates a message saying that the
Number of columns in the tables does not match. But I thought the whole point of a union query was to allow this!

Hope someone can shed some light. Thanks

SELECT *
FROM
BOE LEFT JOIN indices ON bedmate=indices.date
UNION select *
from
technicals LEFT JOIN boe ON technicals.date=boe.date;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-28 : 08:24:34

You can't do a SELECT * UNION for tables that have different column names.

1) Decide which columns to retreive
2) SELECT <col list> FROM Boe UNION ALL SELECT <col list> FROM Indices UNION ALL SELECT <col list> FROM Technicals

E 12°55'05.25"
N 56°04'39.16"

LucasLondon
Starting Member

3 Posts

Posted - 2007-07-29 : 09:52:50

Hello Peso,

Thanks for this. It sounds like the union query approach is not really going to work for me as a couple of my tables as about 40 fields! And this is not really a one off query - some background - basicly I'm regulary importing 3 CSV files into Access and the number of feilds in each file changes with each import. I am now trying to find a way to consolidate the three files into one (which is the whole reason for importing into access), and using the date feild as the basis to align the data from the three files. However in my final file I need to see all columns from the three tables along with all dates, not just where there is a match in date accross all three.

Does anyone know if there is any other way to do a full outer join in Access? I know this can be easily done in other databases (Oracale, SQL server etc)?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-29 : 10:22:54

You don't need a full outer join:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx

You much better off writing the query more logically from scratch, not trying to "simulate" what happens when you use a full outer join.

If you need help with this, post some details here and we can assist.

- Jeff
http://weblogs.sqlteam.com/JeffS

pootle_flump

1064 Posts

Posted - 2007-07-29 : 13:19:39

http://www.dbforums.com/showthread.php?p=6286619#post6286619

LucasLondon
Starting Member

3 Posts

Posted - 2007-08-01 : 09:13:49

OK,

I am really confused now. Let me just get the facts right, it seems:

1) Access does not support the FULL OUTER join clause - we all agree on this

2) However, you can simulate the FULL OUTER join functionality by performing three different joins, and then UNION together the three different result sets. Where Set 1 would be created using an INNER JOIN, Set 2 would be created with a LEFT OUTER JOIN and Set 3 would be created using a RIGHT OUTER JOIN.

3) But I keep hearing that you cannot use unions unless the number of columns in the three tables are the same - implying option two will not work for me!

So for me the question becomes: Is there anyway I can get the same results from a full outer join without using the union function so that it will work even though the numbers of columns are different accross the table?

Thanks,

Lucas

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-01 : 09:32:36

Lucas -- or, you can read the article I posted and see if the examples given make sense to you. You should not "simulate" a FULL OUTER JOIN -- you should write shorter, clearer, more efficient and accurate SQL.

If you need to union two results, but they have differing columns, then just specify NULLS for columns that are missing from either table.

i.e.,

if Table1 has columns A,B, and C
and Table2 has columns B, C,D and E

you can UNION the two together like this:

select A,B,C, Null as D, Null as E
from Table1
UNION ALL
select Null as A, B, C, D, E
from Table2

(note: always use UNION ALL because it is more efficient than UNION).

Do you see how that works? You can specify a value of 0 or "" or whatever you need for the missing columns instead of NULL if you like, just be sure to stay consistent with the column's data type.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources