How can I write a MySQL query that will only return duplicate values, WITHOUT using aggregate functions?

Question

I have a very basic table that consists of worker names and manager names:

CREATE TABLE Work (
Worker_name varchar(50) NOT NULL 
Manager_name varchar(50) NOT NULL;

This table allows a worker to be his own manager, a manager to be in charge of multiple workers, etc.

I'm supposed to write a query that will show every manager who manages at least 2 different employees, BUT I can't use aggregate commands or GROUP BY.

If somebody could give me a hint that would be great. I like figuring things out but this has really got me stumped.

Thanks, Andrew

Answer 1

This query is a little tedious, but it should do what you need it to do, or at least get you on the right track :)

SELECT w1.* FROM Work w1
    INNER JOIN Work w2 ON w2.Manager_name = w1.Manager_name AND w2.Worker_name != w1.Worker_name
    INNER JOIN Work w3 ON w3.Manager_name = w1.Manager_name AND w3.Worker_name != w2.Worker_name
    WHERE w1.Manager_name = w1.Worker_name