>>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'
>>	)
>>
>>

>
>Timothy,
>Might there be a possibility that the subquery returning any null values? It is documented that when a subquery returning null with an In query might produce unexpected results. Here is the exact phrase from technet:
>
>"Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results."
>
>I would try the same query with a 'NOT EXISTS' (and if that doesn't help return all the columns to see the unexpected matches):
>

>-- 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   NOT EXISTS (
>	SELECT *
>	FROM   [Secure].[UserGlobalRole] ugr
>	WHERE  ugr.GlobalRoleFK = g.GlobalRoleID AND
>		ugr.ApplicationFK = '785df211-4c61-4b6c-8302-9934711bacd7' AND
>		ugr.UserFK = '972C23B2-3FAB-43F3-8946-576F15A138B5' 
>	);
>

>
>PS: In your other message, you are explicitly removing any duplicates that may exist with a union.

I am using a union in the complete query. The first part of the union retrieves exactly as expected. The second part gets the duplicates regardless if used as part of the union or run separately. That is why I thought it was ok to omit it from my question.

If I execute the subquery using the same User foreign key above, I get no records. If I execute for a user who actually does have a few Global Roles, then I get records, but nothing unexpected.

I will make some tries with 'NOT EXISTS' and see if there is any difference.
Thanks