had a specific SQL Query question.
have rows like this
scanID | status | empID
1 1 80
1 2 22
1 3 34 379
1 4 22
2 1 73
2 2 22
2 3 92 379
now i want to return all scanIDs in stage 3... which is done with...
"WHERE scanID in (SELECT scanID as SumStatus FROM scan_status sls WHERE sls.status=3 )"
return their sum of what status they are in... which is done with...
"SUM(status) as SumStatus" and "GROUP BY scanID"
so far query looks like:
SELECT scanID, SUM(status) as SumStatus FROM scan_status sls WHERE scanID in (SELECT scanID as SumStatus FROM scan_status sls WHERE sls.status=3 ) GROUP BY scanID
now this will bring back this dataset:
scanID | SumStatus
Now i want to add one more bit of info. In the row that has the stage 3, has info on who authored the document in this 'status stage' and also scanned it (which is usually same person but still could be different), hence the 2 numbers. I would like to take this 2nd number of who scanned it, and show their fullname by joining it with employee table... but since its a Group By Query, the info for 3rd stage is lost.
Not sure, what to do. heres an example of how i would guess it to be done, but looks inefficient and... doesnt work hehe.
SELECT scanID, SUM(status) as SumStatus, ( SELECT concat(e.emplname , ', ', e.empfname) as empname FROM intranet.employee e WHERE e.ID= ( SELECT SUBSTRING( sls.empID , (LOCATE(' ', sss.empID)+1) ) FROM scan_status sss WHERE sss.status=3 AND sss.scanID=???getCurrentIDFromRowBeingLookedAt???) ) WHERE scanID in (SELECT scanID as SumStatus FROM scan_status sls WHERE sls.status=3 ) GROUP BY scanID
and if anyone has better ideas just let me know, at worst i can always like do a second lookup in my php code (by getting the scanID while looping rows in php, and then look up up that '2nd name' in employee table)