Complex SQL query... [modified]
-
SQL novice here.. Hi, I'm having trouble formulating an SQL query and tbh I'm not even sure if its possible to do what I'm trying to do [without a cursor]. I have two tables with a standard 1->M relationship. If a certain field in ALL the linked records are set to a certain value, I need to update a field in the parent record, if the field is set to a different value in one of the linked records, i do nothing. How should I phrase the query? Edit for clarification: Lets say it is a Client table, with a related Tasks table, and the intermediate ClientTasks table. If all the Tasks related to a Client are marked as done, I need to update a field in the Client's record to mark the client as having no outstanding tasks. Thanks in advance..
modified on Tuesday, June 17, 2008 4:26 AM
-
SQL novice here.. Hi, I'm having trouble formulating an SQL query and tbh I'm not even sure if its possible to do what I'm trying to do [without a cursor]. I have two tables with a standard 1->M relationship. If a certain field in ALL the linked records are set to a certain value, I need to update a field in the parent record, if the field is set to a different value in one of the linked records, i do nothing. How should I phrase the query? Edit for clarification: Lets say it is a Client table, with a related Tasks table, and the intermediate ClientTasks table. If all the Tasks related to a Client are marked as done, I need to update a field in the Client's record to mark the client as having no outstanding tasks. Thanks in advance..
modified on Tuesday, June 17, 2008 4:26 AM
can u clarify Your question regards Joe
-
can u clarify Your question regards Joe
-
hi from your clarification i understand that you have to update a master table an a child table...you can handle this either in Stored procedure or in Code itself Regards Joe
-
SQL novice here.. Hi, I'm having trouble formulating an SQL query and tbh I'm not even sure if its possible to do what I'm trying to do [without a cursor]. I have two tables with a standard 1->M relationship. If a certain field in ALL the linked records are set to a certain value, I need to update a field in the parent record, if the field is set to a different value in one of the linked records, i do nothing. How should I phrase the query? Edit for clarification: Lets say it is a Client table, with a related Tasks table, and the intermediate ClientTasks table. If all the Tasks related to a Client are marked as done, I need to update a field in the Client's record to mark the client as having no outstanding tasks. Thanks in advance..
modified on Tuesday, June 17, 2008 4:26 AM
There are several ways, but the quickest (assuming decent indexing) should be along the lines of
update client
set alldone = 'Y' -- signifies no outstanding tasks
from client c
where not exists (select 1 from clienttasks t where t.clientid = c.clientid and t.alldone = 'n')This may not mathc your tables, but you should get the general idea I hope.
Bob Ashfield Consultants Ltd