Sql-server – Writing a query to find all employee salaries that are higher than their managers

sql serversubquery

I have two tables with some dummy data. I need to find all employees that make more than their managers. I was led to believe the point of the exercise is to learn how to do subqueries.

I can isolate the managers, their salaries, and the companies they work for but I don't know how to proceed to compare their employee salaries to their managers in SQL. The error that gets kicked back to me is that "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Which I understand but if I can't pass at least the manager's name and salary from a subquery then I don't know how to solve the problem.

If someone can point me towards a similar post that was answered or some reading that can provide some insight, I'd be very grateful.

My query getting info on the managers

SELECT DISTINCT w.salary, w.pname, w.cname
FROM WorksFor w
INNER JOIN Manages m
ON w.pname = m.mname

Employee/Company Table

CREATE TABLE WorksFor (
employerID int identity(1,1) primary key,
pname varchar(30),
cname varchar(20),
salary int
);

INSERT INTO WorksFor(pname,cname,salary)
VALUES
('John Smith','BigStore',27500),
('Jane Doe','SmallStore',19000),
('Adam Scott','BigStore',50000),
('Bonnie Noel','SmallMfg',25000),
('Cassie Johnson','BigStore',35000),
('Donald Eckerson','SmallStore',29000),
('Erin Joel','SmallMfg',49000);

CREATE TABLE Manages (
manageID int identity(1,1) primary key,
pname varchar(30),
mname varchar(30)
);

Manager Table

INSERT INTO Manages(pname,mname)
VALUES
('John Smith','Adam Scott'),
('Jane Doe','John Doe'),
('Bonnie Noel','Erin Joel'),
('Cassie Johnson','Adam Scott'),
('Donald Eckerson','John Doe');

Best Answer

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like:

CREATE TABLE Workers 
(
    WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID)
    , Salary NUMERIC(10,2) 
);

To add some sample data for demo purposes:

INSERT INTO Workers VALUES (NULL, 1000000);
INSERT INTO Workers VALUES (NULL, 50000);
INSERT INTO Workers VALUES (1, 40000);
INSERT INTO Workers VALUES (1, 1000001);
INSERT INTO Workers VALUES (3, 40002);
INSERT INTO Workers VALUES (2, 29999);
INSERT INTO Workers VALUES (2, 60000);
INSERT INTO Workers VALUES (5, 60000);

And finally, the query to return workers who make more than their boss:

SELECT *
FROM dbo.Workers;

SELECT W.WorkerID
    , W.Salary
    , Bosses.WorkerID AS BossID
    , Bosses.Salary AS BossSalary
FROM dbo.Workers W
    INNER JOIN dbo.Workers Bosses ON W.ManagedByWorkerID = Bosses.WorkerID
        AND Bosses.Salary < W.Salary;

The results:

enter image description here

Related Question