471,888 Members | 2,215 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,888 software developers and data experts.

full outer join on two tables

i have two tables, Event1 and Event2.
They both have fields called contactID and eventID in them.
Some of the contactID's between the tables are equal and some are not.
I want to do a full outer join on contactID so i can get all the
contactID's in the query.

i know access doesn't support full outer joins so there must be a way to
do it without using one.

how do you do it in access 2000?

thanks in advance,

Cory
Nov 13 '05 #1
2 7339
Cory,
Create a union of a left and a right join, so:
SELECT ACTIVE_EVENT.CONTACTID, ACTIVE_EVENT.EVENTID
FROM EVENT1 AS ACTIVE_EVENT
LEFT JOIN EVENT2 AS CLOSED_EVENT
ON ACTIVE_EVENT.CONTACTD = CLOSED_EVENT.CONTACTID
AND ACTIVE_EVENT.EVENTID = CLOSED_EVENT.EVENTID
UNION
SELECT CLOSED_EVENT.CONTACTID, CLOSED_EVENT.EVENTID
FROM EVENT1 AS ACTIVE_EVENT
RIGHT JOIN EVENT2 AS CLOSED_EVENT
ON ACTIVE_EVENT.CONTACTD = CLOSED_EVENT.CONTACTID
AND ACTIVE_EVENT.EVENTID = CLOSED_EVENT.EVENTID

"Cory" <no@no.com> wrote in message
news:1W******************@news02.tsnz.net...
i have two tables, Event1 and Event2.
They both have fields called contactID and eventID in them.
Some of the contactID's between the tables are equal and some are not.
I want to do a full outer join on contactID so i can get all the
contactID's in the query.

i know access doesn't support full outer joins so there must be a way to
do it without using one.

how do you do it in access 2000?

thanks in advance,

Cory

Nov 13 '05 #2
Cory <no@no.com> wrote in
news:1W******************@news02.tsnz.net:
i have two tables, Event1 and Event2.
They both have fields called contactID and eventID in them.
Some of the contactID's between the tables are equal and some
are not. I want to do a full outer join on contactID so i can
get all the contactID's in the query.

i know access doesn't support full outer joins so there must
be a way to do it without using one.

how do you do it in access 2000?

thanks in advance,

Cory


I create a union query for the desired field from each table, and
then left outer join that table back to each source.

Bob Quintal
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Dave | last post: by
7 posts views Thread by alexcn | last post: by
3 posts views Thread by Martin | last post: by
4 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by Lee | last post: by
2 posts views Thread by dskillingstad | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.