I have 3 related tables representing objects: clients, users and leads. Users have type 'User' or 'Admin', can create leads and serve clients. In schema terms, Users.Client references Clients.Id in a many-to-one relationship, and Leads.CreatedBy references Users.Username in a many-to-one relationship. Sample 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)
);
I'm writing a query to show a user their leads. Users of type 'User' should only be able to view only the leads they've created. Users of type 'Admin' should be able to see all leads for their client (but not for other clients). What query will fetch rows from the Leads table according to these restrictions? I've checked other Q&As, but I couldn't figure out how to apply them to the situation described above.
I tried the following:
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
)
However, it generates 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.
To round out the example, some sample 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')
;
All the above is available as a live example.
Without the CASE expression, the query generates no errors, but doesn't follow all the restrictions (all leads for a client are returned for users of type User):
SELECT *
FROM Leads
WHERE createdby IN (
SELECT username
FROM users
WHERE client=(
SELECT client
FROM users
WHERE username='fred'
)
)
This can be seen in the results shown in another live example.
