Question about a SQL Query i am making

[arianhojat]

Hello,
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:
[code]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[/code]

now this will bring back this dataset:
scanID | SumStatus
1 10
2 6

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.
[code]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
[/code]

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)

[arianhojat]

yay actually figured a way to do it without 2nd query.
added this...
(SELECT SUBSTRING(sss.empID , (LOCATE(' ', sss.empID)+1)) FROM scan_status sss WHERE sss.scanID=sls.scanID AND sss.status=3)=e.ID

[code]SELECT scanID,  c.Cltsort, SUM(status) as SumStatus, concat(e.emplname , ', ', e.empfname) as empname

FROM scan_status sls
INNER JOIN scanlog sl ON sls.scanID=sl.ID
INNER JOIN intranet.employee e ON
(SELECT SUBSTRING(sss.empID , (LOCATE(' ', sss.empID)+1)) FROM scan_status sss WHERE sss.scanID=sls.scanID AND sss.status=3)=e.ID
INNER JOIN intranet.clients c ON sl.ClientID=c.ID

WHERE

scanID in (SELECT scanID as SumStatus FROM scan_status sls WHERE sls.status=3 )

GROUP BY scanID[/code]