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
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) );
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');