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 Leads.*
  FROM Leads
    INNER JOIN Users AS creator
      ON Leads.CreatedBy = creator.Username
    INNER JOIN Users AS viewer
      ON    viewer.Id = creator.id
         OR (viewer.Username = 'Sathar' AND viewer.type = 'Admin' AND viewer.Client = creator.Client)
  WHERE viewer.Username = 'Sathar'
;


Id
Name
Company
Profession
CreatedBy


1
A. Person
team lead
A Co
Sathar

2
A. Parrot
team mascot
B Co
Sathar

6
John Q. Employee
employee
F Co
fred

7
Jane Q. Employee
employee
G Co
fred

Id	Name	Company	Profession	CreatedBy
1	A. Person	team lead	A Co	Sathar
2	A. Parrot	team mascot	B Co	Sathar
6	John Q. Employee	employee	F Co	fred
7	Jane Q. Employee	employee	G Co	fred