Solved

help with joins (inner / outer)

Posted on 2008-10-22
5
222 Views
Last Modified: 2010-03-20
So here's the scenario, I'm combining two tables to create a third table.  They are joined by an ID field.
Sometimes Table B does not have the ID to coincide with Table A, which is fine.  The record will still show in the new table, with a blank field from Table B. (inner join)

However, sometimes the ID is missing from Table A, and it is in Table B.  (outer join).  I need to be able to see these records as well on the final table.  But instead the Table A field will be blank.

Example:

TABLE A
ID    FIELD A
1     Hello
3     Name
4     Is
5     Sam

Table B
ID    FIELD B
1     The
2     Fox
3     Jumped
5     Fence

In the end I want to see:

ID      FIELD A          FIELD B
1       Hello               The
2                              Fox
3       Name              Jumped
4       Is
5       Sam                Fence

is this possible, or perhaps I need to do two queries?
0
Comment
Question by:NO_CARRIER
  • 3
5 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22777784
Select FieldList
From TableA A Full Outer Join TableB B ON A.ID = B.ID

This will get all data from BOTH tables.
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 22778048
Thanks, I'll try it.  Never even knew there was such a thing as full outer join. :)
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 22778366
I don't think this will work in Acces.   Seems like it only understand LEFT, RIGHT and INNER joins.
0
 
LVL 22

Accepted Solution

by:
dportas earned 125 total points
ID: 22778633
You'll have to union a pair of joins:

SELECT A.ID, A.A, B.B
FROM TableA AS A LEFT OUTER JOIN TableB AS B ON A.ID = B.ID
UNION
SELECT B.ID, A.A, B.B
FROM TableB AS B LEFT OUTER JOIN TableA AS A ON A.ID = B.ID;

Alternatively, you might want to get a better DBMS. :)
0
 
LVL 1

Author Comment

by:NO_CARRIER
ID: 22779502
Thanks...
Wish we could get a better DBMS---but I don't make the decisions, I just work here. :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now