Attention: We are retiring the ASP.NET Community Blogs. Learn more >

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.

4 Comments

  • I don't have access to a database server to test this, but won't this do the trick?



    SELECT

    r.Name, r.Description, COUNT(u.*)

    FROM

    SystemRoles r

    LEFT JOIN

    SystemUserRoles u

    ON r.Name = u.RoleName

    GROUP BY

    r.Name, r.Description

  • count(u.*) throws incorrect sytax. Changing it to count(u.RoleName) worked fine in my test, though:



    SELECT

    r.Name, r.Description, COUNT(u.RoleName)

    FROM

    SystemRoles r

    LEFT JOIN

    SystemUserRoles u

    ON r.Name = u.RoleName

    GROUP BY

    r.Name, r.Description

  • Wow, you guys are fast...even at almost 2am (well, my time anyway)



    Thanks for the responses from both of you. It looks like I was just overlooking something obvious.



    Thanks again both Andreas and Jon.

  • For other speedy sql help:

    #sql on efnet



    Responses usually within 5 minutes :)

Comments have been disabled for this content.