Solved Insert records where ID combination not already present (JOIN expression not supported)

[AOB]

I'm trying to build a query that will insert new records into a table, which is effectively two columns of ID's (essentially foreign keys to/from two other tables) where the combination doesn't already exist.

Basically, I have a source file/table, to which I join on respective fields to identify the respective ID's, and then I want the combinations that don't already exist in the junction table, to be inserted.

This is the basic query I've tried :

INSERT INTO tblOwners ( GroupID, PersonID, Type )

SELECT G.GroupID AS GroupID, P.PersonID AS PersonID, S.Type AS Type

FROM ((tblSource AS S
INNER JOIN tblPeople AS P ON S.Person = P.Person)
INNER JOIN tblGroups AS G ON S.GroupName = G.GroupName)
LEFT JOIN tblOwners AS O ON G.GroupID = O.GroupID AND P.PersonID = O.PersonID

WHERE O.GroupID IS NULL;

But this doesn't work because :

JOIN expression not supported

I'm not sure (but presume) that's because of the double criteria on the final LEFT JOIN and/or the fact those criteria span two different tables on the "left" side but either way, Access won't let me do it. Have tried parenthesising the ON clause but makes no difference.

I presume this is the kind of query that could be achieved with some kind of WHERE NOT EXISTS clause but I can't wrap my head around how such a clause should be written (have done plenty of WHERE IN / WHERE NOT IN clauses but that's only checking for a single ID in a subquery, not combinations of multiple ID's)

Also - I know that this could be very easily overcome by simply making GroupID and PersonID a composite primary key on tblOwners (they already are...) and simply running the query without the final LEFT JOIN and WHERE clause and letting the constraint block any inserts that already exist.

But - I really want to understand how this kind of query can be written without relying on that, if possible?

Thanks!

 

[Josef P.]

(not tested):

SELECT G.GroupID AS GroupID, P.PersonID AS PersonID, S.Type AS Type
FROM (tblSource AS S
   INNER JOIN tblPeople AS P ON S.Person = P.Person)
   INNER JOIN tblGroups AS G ON S.GroupName = G.GroupName
WHERE
   not exists (
       select 1 from tblOwners O where O.GroupID = G.GroupID and O.PersonID = P.PersonID
   )

OR

Select D.GroupID, D.PersonID, D.Type
from (
   SELECT G.GroupID AS GroupID, P.PersonID AS PersonID, S.Type AS Type
   FROM (tblSource AS S
   INNER JOIN tblPeople AS P ON S.Person = P.Person)
   INNER JOIN tblGroups AS G ON S.GroupName = G.GroupName
) As D
LEFT JOIN tblOwners AS O ON D.GroupID = O.GroupID AND D.PersonID = O.PersonID

WHERE O.GroupID IS NULL;

 

[arnelgp]

lso - I know that this could be very easily overcome by simply making GroupID and PersonID a composite primary key on tblOwners (they already are...) and simply running the query without the final LEFT JOIN and WHERE clause and letting the constraint block any inserts that already exist.

plus the Type field, will be the easiest to construct.
just cross joins your table.