how to combine the rows of a multi row sub query with a conditional statement to be used in In in T-sql

Question

I'm trying to get the records of the Parent Department if exists from the view PayrollViewas well the child departments provided DepartmentSecurityMode=2. The rows are filtered on parent Department Id. This is what I tried but it shows me the error

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

DECLARE @LoggedInUserId   UNIQUEIDENTIFIER
                         = 'ff333441-bc4f-4f3d-b992-aad17baa9ebc'
DECLARE @LoggedInRoleId   UNIQUEIDENTIFIER
                         = '2cb31771-7cbc-4f38-b558-804308d908c7'
DECLARE @DepartmentId   UNIQUEIDENTIFIER
                       = '989fd98a-33ae-4ce1-8bbd-ed51ac46137d'
DECLARE @PeriodId   UNIQUEIDENTIFIER = 'e7e52d21-90ad-4f2d-adc2-3f5399a032dd'

SELECT *
FROM PayrollView pv
INNER JOIN Departments d
ON pv.DepartmentId = d.SysId
CROSS JOIN AppRoles r
LEFT JOIN UserDepartmentView rd
ON d.SysId = rd.DepartmentId
AND rd.RoleId = @LoggedInRoleId
AND rd.UserId = @LoggedInUserId
WHERE PeriodId = @PeriodId
AND r.SysId = @LoggedInRoleId
AND 
(
    r.DepartmentSecurityMode = 2
    AND D.SysId IN
    ( 
        (
            SELECT SysId
            FROM Departments ds
            WHERE ds.ParentHierarchy LIKE
             '%' + cast (@DepartmentId AS VARCHAR (50)) + '%'
        )
        ,
        --This is where I want to combine both the child as well the parent.
        (
            CASE
                WHEN @DepartmentId = '00000000-0000-0000-0000-000000000000' THEN D.SysId
                ELSE @DepartmentId
            END
        )
    )
)

Any suggestions are greatly appreciated.

Answer 1

I don't fully understand your requirement, but as @Johey points out in his answer, there is an issue with your IN statement. Try replacing logic for D.SysId in with this code / let us know if this does what you'd hoped...

AND D.SysId IN
( 
    SELECT SysId
    FROM Departments ds
    WHERE ds.ParentHierarchy LIKE '%' + cast (@DepartmentId AS VARCHAR (50)) + '%'

    UNION

    SELECT CASE
        WHEN @DepartmentId = '00000000-0000-0000-0000-000000000000' THEN D.SysId
        ELSE @DepartmentId
    END
)

Answer 2

You are trying something weird there: with the condition <expression> IN (...) you can filter records that exist in a list of items. That list can be values separated by commas OR a subquery. You are combining both which is (according to my knowledge) not possible.

So you have to separate these two techniques...

Update:

@JohnLBevan 's answer is an option, but I think you could simplify the logic by doing this:

AND (
      -- don't filter if no department provided 
      @DepartmentId = '00000000-0000-0000-0000-000000000000'

      OR 

      -- filter on the given department
      D.SysId IN ( 
                    SELECT SysId
                    FROM Departments ds
                    WHERE ds.ParentHierarchy LIKE '%' + cast (@DepartmentId AS 
VARCHAR (50)) + '%'
                  )
)