SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- problem set-up: schema & data
--- schema
CREATE TABLE Clients (
  Id INT IDENTITY PRIMARY KEY,
  Name VARCHAR(32) NOT NULL
);

CREATE TABLE Users (
  Id INT,
  Username VARCHAR(32) NOT NULL,
  Type VARCHAR(8) NOT NULL CHECK (Type IN ('Admin', 'User')),
  Client INT NOT NULL,
  PRIMARY KEY (Username),
  UNIQUE (id),
  FOREIGN KEY (Client) REFERENCES Clients (Id)
);

CREATE TABLE Leads (
  Id INT IDENTITY PRIMARY KEY,
  Name VARCHAR(64),
  Company VARCHAR(64),
  Profession VARCHAR(64),
  CreatedBy VARCHAR(32) NOT NULL,
  FOREIGN KEY (CreatedBy) REFERENCES Users (Username)
);
--- data

SET IDENTITY_INSERT Clients ON;

INSERT INTO Clients (Id, Name)
  VALUES
(1, 'IDM'),
(2, 'FooCo')
;

SET IDENTITY_INSERT Clients OFF;

INSERT INTO Users (Id, Username, Type, Client)
  VALUES
(1, 'Sathar', 'Admin', 1),
(2, 'bafh', 'Admin', 1),
(3, 'fred', 'User', 1),
(4, 'bloggs', 'User', 1),
(5, 'jadmin', 'Admin', 2),
(6, 'juser', 'User', 2)
;


INSERT INTO Leads (Name, Company, Profession, CreatedBy)
  VALUES
('A. Person', 'team lead', 'A Co', 'Sathar'),
('A. Parrot', 'team mascot', 'B Co', 'Sathar'),

('Alice Adams', 'analyst', 'C Co', 'juser'),
('"Bob" Dobbs', 'Drilling Equipment Salesman', 'D Co', 'juser'),
('Carol Kent', 'consultant', 'E Co', 'juser'),

('John Q. Employee', 'employee', 'F Co', 'fred'),
('Jane Q. Employee', 'employee', 'G Co', 'fred'),

('Bob Howard', 'Detached Special Secretary', 'Capital Laundry Services', 'jadmin')
;
16 rows affected

-- problematic statement: generates error "Subquery returned more than 1 value."

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
  )
;
Msg 512 Level 16 State 1 Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.