Subquery returned more than 1 value with WHERE CASE-CodePudding

There are users in a client, and these users have type ('User' or 'Admin'). They save their leads. If the user is type 'Admin' then he could able to see all the records(in the client) and if the User is type 'User' he should only the records saved by him.

This query works fine if I remove CASE but it gives me below error with CASE

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

Read all the answers but didn't worked for me, how can I able to acive this, query is correct I believe, is there any work around to achieve this?

SELECT * 
  FROM Leads 
  WHERE createdby IN (
    CASE 
      WHEN (SELECT type 
              FROM users 
              WHERE username='Sathar'
           )='Admin' 
        THEN (
          SELECT username 
            FROM users 
            WHERE client=(
              SELECT client 
                FROM users 
                WHERE username='Sathar'
        )   )
      ELSE 'Sathar'
    END
  )

SELECT * FROM Leads where createdby in (select username from users where client=(select client from users where username='Sathar'))

This works fine, problem is only with CASE

CodePudding user response：

This should be done as an if else not as a case, you can do it like:

if (SELECT type FROM users WHERE username='[the username]') = 'Admin'
begin
    --The records you allow for admin to see
end
else
begin
    --The records you allow for non admin to see
end

CodePudding user response：

Use this :

select * from Leads where createdby in (case when (select TOP 1 type from 
users where username='Sathar')='Admin' then (select username from users 
where client=(select TOP 1 client from users where username='Sathar' )) else 
'Sathar' end);

select type from users where username='Sathar' will give only one type so better use TOP 1 for it and same goes for select client from users where username='Sathar'