I've got an SQLite database that contains 3 tables: JobPlans, Tasks, Relationships.
Each JobPlan can have multiple Tasks associated with it.
Each Task can have one Relationship associated with it (actually more, but for the purposes of this discussion we'll keep it simple).
I'm writing a function to assign corresponding Relationship records to the Tasks that do not already have one. The values assigned to the Relationship records are handled differently if all Tasks for a JobPlan are missing a corresponding Relationship record versus if any of them do have a corresponding Relationship record.
Ie. the very first time the function is called, no Tasks will have a Relationship record, so all will be processed using formula "A". Subsequent calls to the function will process tasks using formula "A" only if all Tasks for a given JobPlan are missing Relationship records. If at least one Task pertaining to a JobPlan has a Relationship record, the function must use formula "B" to assign Relationship records to Tasks for that JobPlan.
I can write a SELECT statement to find all Tasks that are missing a corresponding Relationship record:
but what I'd like to be able to do is write a single SELECT statement that filters that selection so that if any Tasks in a JobPlan have corresponding Relationships (Relationships.Value IS NOT NULL), then all Tasks associated with that JobPlan that are missing corresponding Relationships are filtered out (to be processed separately).
I'm guessing there is a way to do this, but I'm struggling to figure it out. Ideas?
Each JobPlan can have multiple Tasks associated with it.
Each Task can have one Relationship associated with it (actually more, but for the purposes of this discussion we'll keep it simple).
I'm writing a function to assign corresponding Relationship records to the Tasks that do not already have one. The values assigned to the Relationship records are handled differently if all Tasks for a JobPlan are missing a corresponding Relationship record versus if any of them do have a corresponding Relationship record.
Ie. the very first time the function is called, no Tasks will have a Relationship record, so all will be processed using formula "A". Subsequent calls to the function will process tasks using formula "A" only if all Tasks for a given JobPlan are missing Relationship records. If at least one Task pertaining to a JobPlan has a Relationship record, the function must use formula "B" to assign Relationship records to Tasks for that JobPlan.
I can write a SELECT statement to find all Tasks that are missing a corresponding Relationship record:
Code:
SELECT JobPlans.Name, Tasks.RowID, Tasks.UID, Relationships.Task_ID FROM Tasks INNER JOIN JobPlans ON Tasks.JobPlan_ID = JobPlans.RowID LEFT OUTER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID WHERE Relationships.Value IS NULL
I'm guessing there is a way to do this, but I'm struggling to figure it out. Ideas?
Comment