Detecting multiple records associated with a table

Question

Hello,

I have a SQL Server 2008 database that has two tables. These two tables are CoreGroup and CoreGroupMember. Please note, I did not setup these tables. Regardless, the table structure is:

CoreGroup
---------
ID
GroupMember1MemberName
GroupMember2MemberName
GroupMember3MemberName
GroupMember4MemberName

CoreGroupMember
---------------
ID
CoreGroupID
MemberName 

I need to determine how many CoreGroup records are associated with a CoreGroupMember with a specific MemberName. There is one catch that is really throwing me for a loop though. Some CoreGroup records only have one member associated with them. I need to retrieve the CoreGroup records that have multiple CoreGroupMember records where at least one of the records has the specific MemberName. I can't seem to figure out the multiple record part. Can someone please help me?

Thank you!

Answer 1

I'll take a stab at it hoping I've understood the requirements correctly. First, I use a cte to find all groups with multiple members, then use that result set to find groups with your specific member.

with cteMultipleMembers as (
    select cg.ID, COUNT(*) as MemberCount
        from CoreGroup cg
            inner join CoreGroupMember cgm
                on cg.ID = cgm.CoreGroupID
        group by cg.ID
        having COUNT(*) > 1
)
select mm.ID
    from cteMultipleMembers mm
        inner join CoreGroupMember cgm
            on mm.ID = cgm.CoreGroupID
                and cgm.MemberName = @YourMemberName