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

Mark,

One approach is to find the set of all Service Orders that have at least
one Work Order not Open.  Then all Service Orders not in that set must
have all their Work Orders Open.  I leave it to you to put that into
SQL.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us
(SELECT * FROM users WHERE clue > 0)


-----Original Message-----
From: Porter, Mark [mailto:MPorter at acsalaska.com] 
Sent: Friday 2003 Feb 21 12:46
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Checking for existence of a condition in all child
records in SQL


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?

Thanks,

Mark Porter