im writing a stored procedure.... Given a groupID, i want to get all the groups from the groups table and also two extra columns....
the first extra column is UserCount, this goes off to the user's table and pulls out a user count for the group.
the second extra column is GroupCount. This goes away and pulls out all other groups that have a ParentID as the incoming org.
I know the stored procedure I have supplied isn't correct, im suspecting I need to have sub queries and group by's.... but sql isn't my fortÃÂ©.
The reason I need these properties returned this way is so that LinqToSql can create a custom object based on this return.
ALTER PROCEDURE [dbo].[uspGetTreeViewGroups]
-- Add the parameters for the stored procedure here
SELECT *, (SELECT COUNT(*) FROM tblUser u
WHERE u.GroupID = @GroupID
AND u.Enabled = 1) as UserCount,
(SELECT COUNT(*) FROM tblGroup g
WHERE g.ParentID = @GroupID) as ChildCount
FROM tblGroup g
WHERE (g.ParentID IS NOT NULL)
View Complete Post