Query returning unwanted results

[Phil]

Hi!

I have a select query which is using the distinctrow predicate to return
parent rows who's child rows meet the set criteria.

for example:

SELECT DISTINCTROW Clients.Client
FROM Clients LEFT JOIN Contacts ON Clients.ID = Contacts.[Record ID]
WHERE ((Contacts.Date)<#8/20/2006#);


The problem i'm having is, if there are 2 child rows, and one matches the
criteria, and the other doesn't, it will still return the parent row. Is
there a way to only return the parent row if all child rows match the
criteria? And still be able to edit the data via the query?


Thanks!

 

[Michel Walsh]

Hi,


First, your LEFT JOIN is useless, here, since the NULL values it may
introduce are eliminated by the WHERE clause. The where clause should care
about the NULL induced by the LEFT JOIN with something like:

WHERE Contacts.Date<#8/20/2006# OR Contacts.Date IS NULL


Now, back to your original question, that could be done with something like:

SELECT Clients.Client
FROM Clients LEFT JOIN Contacts ON Clients.ID = Contacts.[Record ID]
WHERE Contacts.Date<#8/20/2006# OR Contacts.Date IS NULL
GROUP BY Clients.Client
HAVING COUNT(*)=COUNT(Contacts.[Record ID])



since if there are missing (not matching) rows from Contacts, they will
supply a NULL, from the join, and thus, COUNT(*) which counts all records
won't equal COUNT(Contacts.[Record ID]) which does not count rows where
there is a null.

Alternatively, you can also write:

HAVING COUNT(*) = ( SELECT COUNT(*) FROM ... )


where the sub query explicitly returns the expected count.




Hoping it may help,
Vanderghast, Access MVP