Non-code solution

[Bill]

Two tables, A and B. The two tables contain
an ID where that ID has a one-to-many
relationship from table A to table B. While
somewhat simple in code, I'd like a query that
will return all records selected from table A
having NO corresponding linked records in
table B. E.g., if the ID were used as the
selection criteria in selecting records in
table B, one would get an empty set.

Thanks,
Bill

 

[Rob Parker]

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID
WHERE TableB.ID Is Null;

Rob

 

[Bill]

Thanks Rob, that worked like a charm.
Bill

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID
WHERE TableB.ID Is Null;

Rob

 

[Bill]

Hummmm, can an equivalent query be created
that yields an updatable set? I'd like to use the
SQL Delete statement to delete all the records
that meet the specified criteria.
Bill

 

[John Spencer]

This deletes all records in tableA that don't have a match in TableB

DELETE *
FROM TableA
WHERE TableA.ID in
(SELECT TableA.ID
FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID
WHERE TableB.ID Is Null)

This is a variation could be slower, but should accomplish the same thing
DELETE *
FROM TableA
WHERE Not Exists
(SELECT *
FROM TableB
WHERE TableB.ID = TableA.ID)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bill]

Thanks John. It never occurred to me that one could
essentially nest the select inside the delete. Is there a
count to be had following the delete?

 

[Bill]

John,
Here's the "generic" to the specific:

strSQL = "DELETE * FROM Families WHERE Families.FamilyDonOnly = True And
Families.FamilyID in (SELECT Families.* "
strSQL = strSQL & "FROM Families LEFT JOIN DonRegFam ON Families.FamilyID =
DonRegFam.FamilyID "
strSQL = strSQL & "WHERE DonRegFam.FamilyID Is Null)"

With "FamiliesDonOnly = True" having been added to the WHERE clause.

I get a 3306 runtime error:
You have written a subquery that can return more than one field
without using the EXISTS reserved word in the main queries FROM
clause. Revise the SELECT statement in the subquery to request
only one field.

I'm do not know how to satisfy that request. Am I missing the
obvious here? I've never had the occasion to use a sub-query.
I haven't tried your 2nd suggestion, is that the answer to my
issue here?

Bill

 

[Bill]

John,
Since my last post, I tried the "Not Exist" in the sub-query
approach and that seems to work okay. I'm still not clear as to why
the first form didn't work.
Bill

 

[John W. Vinson]

Because you used IN (SELECT Families.*... - where the * selects ALL of the
fields in the families table. It would have worked had you said IN (SELECT
FamilyID FROM... which just selects the ID field.

John,
Since my last post, I tried the "Not Exist" in the sub-query
approach and that seems to work okay. I'm still not clear as to why
the first form didn't work.
Bill

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Well, that one was staring me in the face and it just
didn't click "hello" on my brain.
Thanks,
Bill

 

[John W. Vinson]

Well, that one was staring me in the face and it just
didn't click "hello" on my brain.

Sometimes the error message just means what it says... rarely enough that it's
really easy to miss when it does!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

If you run the query in VBA module you can check the RecordsAffected property.
It returns a count of records affected for action queries.

Dim Db as DAO.Database
Set Db = Currentdb()
Db.Execute StrSQL
MsgBox db.RecordsAffected & " Records deleted"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County