Click here to Skip to main content
15,917,652 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm new in SQL Server, i'm using SQL Server 2012, i have 2 tables Service and Project and each service may have one or more project, i tried to make a stored procedure that retrieve the service name and the number of projects related to this service, this my SQL Statement:
Create proc [dbo].[SP_ServiceProjectCount]
SELECT s.ServiceName + '  ( '+ CAST( COUNT(p.ProjectID) as nvarchar(255)) + ' )' as ServiceName , s.ServiceID
FROM  Service AS s INNER JOIN Project AS p 
ON s.ServiceID = p.ServiceID
GROUP BY s.ServiceID, s.ServiceName
order by s.ServiceName

and the result is:
Kitchens (2)
Living Rooms (4)
but the services that have no projects are not retrieved .. i want them to be retrieved with 0 count like this:
Kitchens (2)
Living Rooms (4)
Bathrooms (0)

thanks in advance

1 solution

Try this change in your join.
FROM  Service AS s LEFT JOIN Project AS p
Share this answer
Hend Riad 31-Jul-13 23:41pm    
Thanks for you so much it works
ArunRajendra 31-Jul-13 23:49pm    
Maciej Los 1-Aug-13 1:42am    
Raja Sekhar S 1-Aug-13 4:42am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900