SQL Bugger
This feels like a lame way to reach 100 posts but I'm kinda stumped at the moment and figured maybe someone that reads this will be enough of a SQL Wizard to help.
Scenario: I have 2 tables.
1st Table - "SystemRoles" has 2 columns, Name and Description. Name is the Prim. Key.
2nd Table - "SystemUserRoles" has 2 columns also. UserName and RoleName. Together those 2 columns make up a composite Prim. Key. This table's data keep track of users and the roles they are members of.
I want to write a query that will return a result set with 3 columns. Name and Description from SystemRoles and a "MemberCount" column that, for each role, tells me the number of users in that role as found in the SystemUserRoles table. The tricky part is that I need a zero in the MemberCount column of the result set if no users are currently a member for any given role. So I think a simple inner join is out of the question based on that requirement.
If anyone knows how to write a query for this I'd sure appreciate the help. It's late right now and my brain just isn't working. I'll sing praises to your name if you can help me with this.
UPDATE: Here is something I got to work:
SELECT SystemRoles.Name, SystemRoles.Description,
(SELECT COUNT(*)
FROM SystemUserRoles
WHERE RoleName = SystemRoles.Name) AS MemberCount
FROM SystemUserRoles CROSS JOIN
SystemRoles
GROUP BY SystemRoles.Name, SystemRoles.Description
UPDATE on the UPDATE: Andreas and Jon came through. Thanks guys. See this posts comments for the solution.