[AccessD] Checking for existence of a condition in all child records in SQL

Gustav Brock gustav at cactus.dk
Fri Feb 21 12:55:00 CST 2003


Hi Mark

You could join the two tables on the Service Orders PK, then pick
Count(ID) of the child table Where Status = False.
Count(ID) must be zero.
This assumes, of course, that a service order will always have at
least one child record.

/gustav

> I need SQL only answers on this please:

> The parent records here are 'Service Orders' and the child records are 'Work
> Orders'.  Both have a 'Status' column.

> I need to write an SQL statement which shows the 'Service Orders' which have
> all child 'Work Orders' where Status = 'Open'.

> Service Orders which have SOME Open Work Orders are not to be shown.  Only
> Service Orders which have 100% of their Work Orders open.

> Any ideas on how to approach this?




More information about the AccessD mailing list