many-to-many and finding unmatched

[Edward Reid]

OK, so I know the basic trick for finding values on the left side of a
relationship which are not in the right side: use a left join and test for
null in the right-side field. Works any time the relationship is directly
between two tables, either many-to-one or one-to-many. I'm using it to
determine whether an input text field is already in a lookup table. Could
be used for matching PK/FK too.

But how does one do the same thing with a many-to-many relationship? Say I
have

tableA:
pkA - autonumber PK
identA - text no dupes

tableB
pkB - autonumber PK
identB - text no dupes

rel
pkrel - autonumber
pkA - FK
pkB - FK

For any given value of identA, I want the values of identB which are not
currently related to identA's record.

I know how I'd do it in a procedural language: read the records of rel
related to identA, sort them by rel!pkB (actually I'd read rel via an index
keyed by pkA and pkB), then read tableB sequentially, doing a collate to
drop the records which match the rel!pkB values. I'm sure I could do the
same in VBA. But I haven't seen how to do it in pure Access. (Or in SQL,
but I've mostly been looking for an Access solution which doesn't require
explicit SQL.) I can easily see that what I need is a set difference: the
cross-product of tableA X tableB less the many-to-many relation from tableA
to tableB (represented as the two inner joins tableA <-> rel <-> tableB).

This actually arose when I was trying to help someone else with a table
design. I finally figured out that this was the reason it was getting
messy.

Edward

 

[Allen Browne]

1. Create a query using TableA and TableB.
No join. This Cartesian product gives you every possible combination.
Save as Query1.
Close.

2. Create another query using Query1 and table rel.
Outer-join on both pkA and pkB.
Set the criteria for null foreign key.

 

[Edward Reid]

Ah-ha! Got it. Thanks!

2. Create another query using Query1 and table rel.
Outer-join on both pkA and pkB.

Left join here, correct? That's what I did. Recording it here for
posterity, or at least in case anyone runs across this.

Edward