I would have thought you must have a duplicate ug.GlobalRoleFK if the query works ok without the join

>I know this should be obvious to me but today it is not. I cannot see it and am trying to get this sorted so I can move on to something more important.
>
>I have a stored procedure to return rows from a roles table that a user does not already belong in. I am getting duplicate rows. Can someone point me to what it is?
>
>Global Role table holds all the Roles available to multiple applications. The UserGlobalRole table holds the records for which users are in which role for a specific application. They can be in multiple roles for an application and they can be in the same role for multiple applications. This query is only interested in those roles available that he is not already in for this specific application.
>
>

>
>	-- This gives me duplicate rows
>	SELECT
>		ug.[Active],
>		ug.[ApplicationFK],
>		ug.[LastUpdate],
>		g.[Description],
>		g.[Name],
>		g.[Permanent],
>		g.[GlobalRoleId] AS RoleID
>	FROM [Secure].[GlobalRole] g
>	JOIN [Secure].[UserGlobalRole] ug
>	ON g.GlobalRoleId = ug.GlobalRoleFK
>	WHERE g.GlobalRoleID NOT IN
>	(
>		SELECT GlobalRoleFK FROM [Secure].[UserGlobalRole]
>			WHERE ApplicationFK = '785df211-4c61-4b6c-8302-9934711bacd7' AND
>				UserFK = '972C23B2-3FAB-43F3-8946-576F15A138B5'
>	)
>
>
>	-- This works fine
>	SELECT * FROM Secure.GlobalRole
>	WHERE GlobalRoleID NOT IN
>	(
>		SELECT GlobalRoleFK FROM [Secure].[UserGlobalRole]
>			WHERE ApplicationFK = '785df211-4c61-4b6c-8302-9934711bacd7' AND
>				UserFK = '972C23B2-3FAB-43F3-8946-576F15A138B5'
>	)
>
>