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

How about:
SELECT * FROM tblServiceOrders
WHERE Exists (SELECT * FROM tblWorkOrders
WHERE tblWorkOrders.ServiceOrderID = tblServiceOrders.ServiceOrderID
AND Status<>'Open')=False

-----Original Message-----
From: Porter, Mark [mailto:MPorter at acsalaska.com]
Sent: Friday, February 21, 2003 12:46 PM
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


This transmittal may contain confidential information intended solely for
the addressee. If you are not the intended recipient, you are hereby
notified that you have received this transmittal in error; any review,
dissemination, distribution or copying of this transmittal is strictly
prohibited. If you have received this communication in error, please notify
us immediately by reply or by telephone (collect at 907-564-1000) and ask to
speak with the message sender. In addition, please immediately delete this
message and all attachments. Thank you.
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com