A double outer join?

Using an outer join, a query can return all records from Table1 and only
those matching from Table2 (or vice versa). How can I write a query that
will return unmatched records from both sides? For example:

Table1
Key Data
----------
123 Bill
124 Tom
125 Joe

Table2
Key Data
----------
121 Ed
123 Bill
124 Tom

Query Output:
Key T1 T2
------------
121 Null Ed
123 Bill Bill
124 Tom Tom
125 Joe Null

Thanks for any help.

re: A double outer join?

"Randy Harris" <randy@SpamFree.com> wrote in message
news:J%Apb.7527$P%1.6361122@newssvr28.news.prodigy .com...[color=blue]
> Using an outer join, a query can return all records from Table1 and only
> those matching from Table2 (or vice versa). How can I write a query that
> will return unmatched records from both sides? For example:[/color]

It's called a full outer join and Access don't do 'em. So do a union of a
left outer and a right outer:

SELECT Table1.ID, Table1.text, Table2.text
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID

UNION

SELECT Table2.ID, Table1.text, Table2.text
FROM Table1 RIGHT JOIN Table2 ON Table1.ID=Table2.ID;

HTH, Mike MacSween

Why? by the way.

re: A double outer join?

"Randy Harris" <randy@SpamFree.com> wrote in
news:J%Apb.7527$P%1.6361122@newssvr28.news.prodigy .com:
[color=blue]
> Using an outer join, a query can return all records from
> Table1 and only those matching from Table2 (or vice versa).
> How can I write a query that will return unmatched records
> from both sides? For example:
>[/color]
there may be other ways, but I create a union query with one field,
the Key from each table, and do an outer join to each source table
in a second query.

Query 1:
SELECT mykey from table1
UNION select mykey from table2
UNION select mykey from table3
;

Query 2:
SELECT query1.mykey table1.* table2.* from query1 left join
table1...

Bob

re: A double outer join?

"Mike MacSween" <mike.macsween.nospam@btinternet.com> wrote in message
news:3fa6e86d$0$52887$5a6aecb4@news.aaisp.net.uk.. .[color=blue]
> "Randy Harris" <randy@SpamFree.com> wrote in message
> news:J%Apb.7527$P%1.6361122@newssvr28.news.prodigy .com...[color=green]
> > Using an outer join, a query can return all records from Table1 and only
> > those matching from Table2 (or vice versa). How can I write a query[/color][/color]
that[color=blue][color=green]
> > will return unmatched records from both sides? For example:[/color]
>
> It's called a full outer join and Access don't do 'em. So do a union of a
> left outer and a right outer:
>
> SELECT Table1.ID, Table1.text, Table2.text
> FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
>
> UNION
>
> SELECT Table2.ID, Table1.text, Table2.text
> FROM Table1 RIGHT JOIN Table2 ON Table1.ID=Table2.ID;
>
> HTH, Mike MacSween[/color]

Mike, I haven't tried this yet, but I have a question. Won't that give me
double entries for any records where the IDs do happen to match up?
[color=blue]
> Why? by the way.[/color]

Hmm. Two separate applications, two separate databases. Someday I hope to
have them combined into a single database, but in the meantime I have the
users manually entering the PK-ID's from one system into the other as
records get created so that I have something to join them with. To help
correct entry errors, I've written queries that find unmatched records, but
it becomes difficult to find the record that should be linked in the other
database. A single query that shows both sides, with both the matched and
the unmatched records, will help in tracking down errors.

Thanks for the help,
Randy Harris

re: A double outer join?

"Randy Harris" <randy@SpamFree.com> wrote in message
news:HSCpb.7544$P%1.6385647@newssvr28.news.prodigy .com...[color=blue]
>
> "Mike MacSween" <mike.macsween.nospam@btinternet.com> wrote in message
> news:3fa6e86d$0$52887$5a6aecb4@news.aaisp.net.uk.. .[color=green]
> >
> > It's called a full outer join and Access don't do 'em. So do a union of[/color][/color]
a[color=blue][color=green]
> > left outer and a right outer:
> >
> > SELECT Table1.ID, Table1.text, Table2.text
> > FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
> >
> > UNION
> >
> > SELECT Table2.ID, Table1.text, Table2.text
> > FROM Table1 RIGHT JOIN Table2 ON Table1.ID=Table2.ID;
> >
> > HTH, Mike MacSween[/color]
>
> Mike, I haven't tried this yet, but I have a question. Won't that give me
> double entries for any records where the IDs do happen to match up?
>[/color]

Please ignore that, I tried it and it does exactly what you said. Thanks.

re: A double outer join?

"Randy Harris" <randy@SpamFree.com> wrote in message
news:FREpb.7568$P%1.6415316@newssvr28.news.prodigy .com...[color=blue]
> "Randy Harris" <randy@SpamFree.com> wrote in message
> news:HSCpb.7544$P%1.6385647@newssvr28.news.prodigy .com...[color=green]
> >
> > "Mike MacSween" <mike.macsween.nospam@btinternet.com> wrote in message
> > news:3fa6e86d$0$52887$5a6aecb4@news.aaisp.net.uk.. .[color=darkred]
> > >
> > > It's called a full outer join and Access don't do 'em. So do a union[/color][/color][/color]
of[color=blue]
> a[color=green][color=darkred]
> > > left outer and a right outer:
> > >
> > > SELECT Table1.ID, Table1.text, Table2.text
> > > FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
> > >
> > > UNION
> > >
> > > SELECT Table2.ID, Table1.text, Table2.text
> > > FROM Table1 RIGHT JOIN Table2 ON Table1.ID=Table2.ID;
> > >
> > > HTH, Mike MacSween[/color]
> >
> > Mike, I haven't tried this yet, but I have a question. Won't that give[/color][/color]
me[color=blue][color=green]
> > double entries for any records where the IDs do happen to match up?
> >[/color]
>
> Please ignore that, I tried it and it does exactly what you said. Thanks.[/color]

Yes, UNION doesn't return duplicates unless you use UNION ALL

Mike