CODEWITHSUNDEEP
mysql
Brds
Brds

Reputation: 1076

mySQL - possible to select records based on previous records values?

I have a table filled with tasks that users need to complete. Task B can't be started until Task A is completed, so my table looks something like this:

taskID   |   jobID   |   assignedTo   |   taskName   |   completedTime
----------------------------------------------------------------------
1               1           Carl            blah 1          11235512
2               1           Mike            blah 2          13578423
3               1           Steve           blah 3          0
4               2           Alex            blah 4          12481235
5               2           Chris           blah 5          0
6               2           Steve           blah 6          0

Taking a look at Steve, I would want to select the job 1 - taskid 3 entry because every task (within the same job) before him has been completed and now it's his time to do his task for that job. However, I would NOT want to select the job 2 - taskid 6 for Steve because Chris hasn't completed job 2 - taskid 5.

Is there a way to do this in one query? Or would I have to get a list of all of Steve's tasks, then loop through each one and verify that it's the lowest taskID for each job that has a completedTime of 0?

Upvotes: 1

Views: 80

Answers (2)

Serinus
Serinus

Reputation: 196

Are the tasks always added in order? Currently there's no way other than taskID to tell that taskIDs 1 and 2 should be completed before 3.

select top 1 *
from tblTasks 
where completedTime = 0 
 and jobID = (select top 1 jobid from tblTasks where assignedTo = @username and completedTime = 0 order by jobID)
 and assignedTo = @username
 order by taskID;

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65314

SELECT
nowtable.* -- or whatever fields you want
FROM yourtable AS nowtable
LEFT JOIN yourtable AS beforetable
  ON nowtable.jobID=beforetable.jobID 
  AND beforetable.taskID<nowtable.taskID
WHERE nowtable.assignedTo="Steve"
GROUP BY nowtable.taskID
HAVING IFNULL(COUNT(beforetable.taskID),0)=0

Upvotes: 3

Related Questions