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

John W. Colby jcolby at colbyconsulting.com
Fri Feb 21 13:51:00 CST 2003


works for me

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Wortz, Charles
Sent: Friday, February 21, 2003 1:53 PM
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



----------------------------------------------------
Is email taking over your day?  Manage your time with eMailBoss.  
Try it free!  http://www.eMailBoss.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 2532 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030221/322127ad/attachment-0001.bin>


More information about the AccessD mailing list