How to do a left and right join together?

  • Thread starter Thread starter Grd
  • Start date Start date
G

Grd

Hi there,

I have a query and I want both the results I get with option 2 and option 3
which as I understand is the left and right join.

I'm told its possible however there are only three choices.

could anyone advise me - its really something I need

Thanks

Suzanne
 
a 4th unstated option is a "no-join" Cartesian result set: all possible
combinations

Given: one table with employees (100 records) and one table with DayOfWeek
(7 records)
- Add both tables to query. No join between tables
- Add Employee name, Day of week fields to query
- Run query
- result would be a listing of all employees for each day of week ( 700
records)
 
Suzanne,

I think what you are referring to is a "Full Outer Join". Assuming that you
have two tables that are similar, but contain records that may or may not
correspond, and you want to creat a query that has all the records from A and
B. Unfortunately, Access does not have a simple way to accomplish this.

However, the approved workaround is not too difficult.

1. Generally, I start by creating the left join:

SELECT A.Field1, A.Field2, A.Field3, B.Field4
FROM tableA as A LEFT JOIN tableB as B
ON A.Field1 = B.Field1

This will give you all the records from A, and only those from B that match.

2. Then go to the SQL view and add the following:

UNION
SELECT B.Field1, B.Field2, B.Field3, B.Field4
FROM tableB as B LEFT JOIN tableA as A
ON B.Field1 = A.Field1
WHERE A.Field1 IS NULL

3. So the final query would look like

SELECT A.Field1, A.Field2, A.Field3, B.Field4
FROM tableA as A LEFT JOIN tableB as B
ON A.Field1 = B.Field1
UNION
SELECT B.Field1, B.Field2, B.Field3, B.Field4
FROM tableB as B LEFT JOIN tableA as A
ON B.Field1 = A.Field1
WHERE A.Field1 IS NULL

Keep in mind, that in a UNION query, you have to have the same number of
fields in each portion of the union, and the field types must correspond.

If this doesn't answer your need, post back with more info on the two tables
(fields) you want to include in the query, and we'll see what we can do.
 
Works

Thanks
dale

Dale Fye said:
Suzanne,

I think what you are referring to is a "Full Outer Join". Assuming that you
have two tables that are similar, but contain records that may or may not
correspond, and you want to creat a query that has all the records from A and
B. Unfortunately, Access does not have a simple way to accomplish this.

However, the approved workaround is not too difficult.

1. Generally, I start by creating the left join:

SELECT A.Field1, A.Field2, A.Field3, B.Field4
FROM tableA as A LEFT JOIN tableB as B
ON A.Field1 = B.Field1

This will give you all the records from A, and only those from B that match.

2. Then go to the SQL view and add the following:

UNION
SELECT B.Field1, B.Field2, B.Field3, B.Field4
FROM tableB as B LEFT JOIN tableA as A
ON B.Field1 = A.Field1
WHERE A.Field1 IS NULL

3. So the final query would look like

SELECT A.Field1, A.Field2, A.Field3, B.Field4
FROM tableA as A LEFT JOIN tableB as B
ON A.Field1 = B.Field1
UNION
SELECT B.Field1, B.Field2, B.Field3, B.Field4
FROM tableB as B LEFT JOIN tableA as A
ON B.Field1 = A.Field1
WHERE A.Field1 IS NULL

Keep in mind, that in a UNION query, you have to have the same number of
fields in each portion of the union, and the field types must correspond.

If this doesn't answer your need, post back with more info on the two tables
(fields) you want to include in the query, and we'll see what we can do.
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
Click to expand...
 
Thanks George,

The cartesian doesn't work for what I want here but its a very interesting
technique.
Thanks. Dale 'full outer join' gives me the results I'm after.
 
Back
Top