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');
  • 1
    With your design a person can have more than one managers associated. What should the result be then? Persons that make more than all their managers? Or the ones that make more than at least one of their managers? – ypercubeᵀᴹ May 8 '14 at 19:31
  • 1
    And you haven't set up foreign keys, so invalid data can appear (as for example you already have 'John Doe' appear as a manager in Manages.mname while no such employee exists in WorksFor table.) – ypercubeᵀᴹ May 8 '14 at 19:38
up vote 6 down vote accepted

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.