Click here to Skip to main content
15,904,024 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:
SQL
Create proc [dbo].[SP_ServiceProjectCount]
as
begin
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
end

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
Posted

1 solution

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

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