help with joins (inner / outer)

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?
LVL 1
NO_CARRIERAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
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
 
brad2575Commented:
Select FieldList
From TableA A Full Outer Join TableB B ON A.ID = B.ID

This will get all data from BOTH tables.
0
 
NO_CARRIERAuthor Commented:
Thanks, I'll try it.  Never even knew there was such a thing as full outer join. :)
0
 
NO_CARRIERAuthor Commented:
I don't think this will work in Acces.   Seems like it only understand LEFT, RIGHT and INNER joins.
0
 
NO_CARRIERAuthor Commented:
Thanks...
Wish we could get a better DBMS---but I don't make the decisions, I just work here. :)
0
All Courses

From novice to tech pro — start learning today.