Select row if you are current user or substitute-CodePudding

Categories

Category	person
A	Kate
B	Tom

Substitutions

Person	Substitute
Kate	Roxy

I want to get Category, where person is me or my substitute. I have variable with current user, so when current user is Kate is okey:

Select category from Categories where person = ‘Kate’

But if current user is Roxy, I still want to see the same row. It should also work when current user not exists in Substitutions table.

CodePudding user response：

Considering the lack of sample data, I think an EXISTS would likely be the safest bet here:

SELECT C.Category,
       C.person
FROM dbo.Category C
WHERE EXISTS (SELECT 1
              FROM dbo.Substitution S
              WHERE C.person IN (S.Person, S.Substitute)
                AND @YourVariable IN (S.Person, S.Substitute))
   OR C.person = @YourVariable;