Jump to content

Question about a SQL Query i am making


arianhojat

Recommended Posts

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)
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.