I have wrote the following query which extracts all users which are in child departments of current user's department. Current user cames from client app, but I Decalred it here in SQL for tests reason.
DECLARE @UserID INT = 72 SELECT * FROM users WHERE Department_Id IN ( SELECT DISTINCT Id /*, idp*/ FROM departments WHERE idp IN ( SELECT Department_Id FROM users WHERE Id = @UserID ) ) OR Department_Id IN ( SELECT DISTINCT idp FROM departments WHERE idp IN ( SELECT Department_Id FROM users WHERE Id = @UserID ) )
I wanted to select the Id and the idp from departments to have a short query, but when i use this way it returns me an SQL error :
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
This is because my list should contain only one column, but not 2.
Please advice me any way to reduce this query, especially the second part (after OR) which is a copy-paste of first (before OR)