Link to home
Start Free TrialLog in
Avatar of praveen1981
praveen1981Flag for India

asked on

Sql query

Hi,
Here are the three table TableA,TableB, and TableC with following columns.
Table A
EmpId  Name
001        abc
002        bbc
Table B
Id  empid , date, statusId  (This is reference of Statusid from table C)
1    001     ,12-2-2015,   1
2    001  , 13-2-2015,     1
3    001  , 14-2-2015,     2
4    002  , 15-2-2015 ,     3
Table C
statusId  statusName
1      Inprogress
2      Completed
3      Not Open


Now what is the count of inprogress and what is the count of completed and what is the count of Not open for each employee.
Please provide the query.
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

You can use the following query...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid

Open in new window


Saurabh...
SELECT A.Name C.StatusName, COUNT(B.StatusID)
FROM TableB B
    INNER JOIN TableC C ON B.StatusID = C.StatusID
    INNER JOIN TableA  A ON B.Empid = A.EmpId
GROUP BY A.Name, C.StatusName

Open in new window

Ahh Good catch vitor..I missed group by in my statement my bad...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid
group by tb1.name,tb3.statusname

Open in new window

Avatar of praveen1981

ASKER

Hi

I want the result as follows

EmpId  Inprogress Completed NotOpen

001           2                    1                0
002           0                    0                1

could you please suggest.
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can use PIVOT table:
SELECT *
FROM (SELECT A.EmpId, C.StatusName, B.StatusID
		FROM TableB B
			INNER JOIN TableC C ON B.StatusID = C.StatusID
			INNER JOIN TableA  A ON B.Empid = A.EmpId) T
PIVOT
	(COUNT(StatusID) FOR StatusName IN (Inprogress, Completed, [Not Open])
) AS PivotTable

Open in new window

Thanks
Any particular reason for the grade b?? Because i'm assuming your problem was solved on this one properly..