Porter, Mark
MPorter at acsalaska.com
Fri Feb 21 13:23:01 CST 2003
Thanks all - I just couldn't wrap my head around the logic at first. I fixed this using a NOT EXISTS subquery which looked for any 'Open' work orders. Mark -----Original Message----- From: Wortz, Charles [mailto:CWortz at tea.state.tx.us] Sent: Friday, February 21, 2003 9:53 AM To: accessd at databaseadvisors.com Subject: RE: [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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com 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.