"either" join option

[Stevestan1]

There are normally 3 join options in Access:

1. Records must be found in both tables
2. Records must be found in table "x", but not table "y"
3. Records must be found in table "y", but not table "x"

Is there a join option that includes all unique records from both tables?

[nico5038]

Use a INNER JOIN, LEFT JOIN and RIGHT JOIN query and combine them with a UNION.

Access has no OUTER JOIN :-(

Nic;o)

[NeoPa]

Have a look at SQL JOINs for more on this.

Access doesn't support the FULL OUTER JOIN, which is what you're referring to, though other database engines do.

Nico's idea, to overlap multiple joins, can work in almost all cases. It wouldn't need the INNER JOIN, as this is a subset of both the LEFT JOIN and the RIGHT JOIN. That said, it should do the job for you. I mentioned almost all cases, as there is overlap where [Table A] and [Table B] match. To avoid duplicating the records it is necessary to use the UNION version, as opposed to the UNION ALL version, in a case like this. In most cases this is fine, but there can be data sets where there are duplicates occuring validly, which this would effect (by removing the duplicates - even the valid ones).

[nico5038]

Oops, good remark NeoPa. Was anticipating to add an additonal TypeJoin variable in the queries indicating the source (Inner/Left/Right). This would however require the LEFT and RIGHT JOIN to test for a missing key in the "opposite" table.

Nic;o)

[NeoPa]

Good idea Nico.

You could still do this without the INNER JOIN though (unless you needed to use UNION ALL). Any record where both keys were non-Null would be flagged as INNER.

With a UNION ALL, then all types of joins would be required and the WHERE clause would be also be required for the OUTER joins (LEFT JOIN & RIGHT JOIN) to ensure none of the INNER JOIN records were repeated.

[nico5038]

"You could still do this without the INNER JOIN"
No, as you'll get the same "match" for both LEFT and RIGHT, now with a different literal....
The exclusion with a test for the missing foreignkey is essential.

Nic;o)

[NeoPa]

My understanding is that this is only a problem when using UNION ALL. When using the UNION alone, the duplicates are dropped anyway, so it wouldn't matter that the same records are found in both the LEFT and RIGHT OUTER JOINs. The literal in each case would be "INNER JOIN". I did cover the UNION ALL scenario in my post. I suspect that is the one you were expecting to be used for your suggestion.

[nico5038]

When having:

select "match" as Type, * from tblA INNER JOIN ...
UNION 
select "Left", * from tblA LEFT JOIN
UNION 
select "Right", * from tblA RIGHT JOIN
 

Will force the UNION (without "ALL") to find always a difference in the rows that match when not excluding them....

Nic;o)

[NeoPa]

That would result in any matches appearing three times though (As a matching pair would appear in each inner recordset regardless of the JOIN type). Is this your intention?

I would expect something like :

SELECT IIf(tblB.ID Is Null, 'Left', 'Match') AS [Type]
     , tblA.*
FROM   tblA LEFT JOIN tblB
  ON   tblA.ID = tblB.ID
UNION
SELECT IIf(tblA.ID Is Null, 'Right', 'Match')
     , tblB.*
FROM   tblA RIGHT JOIN tblB
  ON   tblA.ID = tblB.ID

Clearly, I've made some basic assumptions about how the tables are linked with an [ID] field, etc. Also, as your example uses fields from only a single table, that both tables hold similar data in the same fields.

For this question I would expect the fields from both [tblA] and [tblB] to be required in the SELECT clause. The question isn't specific on that score, but that would be my guess.

[nico5038]

As always there are multiple ways to achieve the same.

I would add a WHERE clause on the Left/Right join queries to filter only the mismatches. Thus UNION or UNION ALL don't make a difference but you'll need three queries. Your solution needs the roll-up of the UNION, but is indeed shorter :-)

Nic;o)

[NeoPa]

I do understand what you're saying Nico (See post #5), and you're right. There are so many ways to handle this, but the appropriate ones nearly always depend on knowing the precise details of the question (which we rarely do of course). It's always fun discussing things in finer detail with someone who knows the stuff as well as you do.