It's supposed to only display where roster.TeamMemberTypeId = 5
however it is showing other TeamMemberTypeId, such as 3, which are coaches.
I cannot figure out what is wrong here, do you know? If so what is it / how do I fix it so it actually only shows TeamMemberTypeId=5?
Code:
SELECT MembershipNumber 'Mbr #', FirstName, LastName, TeamName, Level FROM ( SELECT DISTINCT person.Id 'PersonId', person.MembershipNumber, person.FirstName, person.LastName, team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 LEFT JOIN entity.Person person ON roster.PersonId = person.Id WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) retrievedRoster WHERE PersonId IN ( SELECT PersonId FROM ( SELECT DISTINCT roster.PersonId 'PersonId', team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) temp GROUP BY PersonId HAVING COUNT( PersonId ) > 1 ) ORDER BY LastName