WHERE not displaying results correctly

Transact-SQL
1

See code, 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?

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
2

the information on TeamMemberTypeId is on attribute.TeamCompRoster and you LEFT JOIN to it. Try changing to INNER JOIN

3

[quote="techsupport15, post:1, topic:4557"]
I cannot figure out what is wrong here, ...[/quote]

If the code was actually written the way you posted it, I can't blame you. :wink:

1 Like
4

Yea, someone else's and needs to be fixed.
I added the TeamMemberTypeID=5 to the SELECT portion.

5

You mean to the WHERE clause. That will also effectively convert the LEFT JOIN to INNER JOIN. But for clarity, i wouldn't do that.

6

Sites such as poorsql.com will let you format your code to make it more readable.

7

Here is one more great service for beautifying SQL code - Online SQL Formatter