> > -- 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,
-- 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.