Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Stuck on sql query to find parent records that have the same child records

John O'TooleAug 11 2010 — edited Oct 31 2010
Oracle 10gR2 Enterprise Edition.

Hi,

I'm trying to write some logic to look for records in a parent table, which have the exact same values in a child table.
This is part of a bigger query, but I'm stuck on this part for now, so I've mocked up some simplified tables below to capture the core of the
problem I'm stuck on.
Let say I've got a parent table Manager, a child table Employee and there's a many to many relationship between them.
The aptly named Join_Table handles the relationship between them. So one manager can manage many employees, one employee can be managed by
many managers.

I've a feeling this is stupidly easy, but I seem to be suffering from a bad bout of brain freeze today!
-- parent table
CREATE TABLE manager (
 id 	number primary key,
 name 	varchar2(100));

-- child table 
CREATE TABLE employee (
 id		number primary key,
 name 	varchar2(100));

-- link table
CREATE TABLE join_table (
 manager_id		NUMBER, 
 employee_id 	NUMBER,
 CONSTRAINT join_table_pk PRIMARY KEY (manager_id, employee_id),
 CONSTRAINT manager_fk FOREIGN KEY (manager_id) REFERENCES manager(id),
 CONSTRAINT employee_fk FOREIGN KEY (employee_id) REFERENCES employee(id) 
 );

-- Insert some managers
INSERT INTO manager (id, name) VALUES (1, 'John');
INSERT INTO manager (id, name) VALUES (2, 'Bob');
INSERT INTO manager (id, name) VALUES (3, 'Mary');
INSERT INTO manager (id, name) VALUES (4, 'Sue');
INSERT INTO manager (id, name) VALUES (5, 'Alan');
INSERT INTO manager (id, name) VALUES (6, 'Mike');

-- Insert some employees 
INSERT INTO employee (id, name) VALUES (101, 'Paul');
INSERT INTO employee (id, name) VALUES (102, 'Simon');
INSERT INTO employee (id, name) VALUES (103, 'Ken');
INSERT INTO employee (id, name) VALUES (104, 'Kevin');
INSERT INTO employee (id, name) VALUES (105, 'Jack');
INSERT INTO employee (id, name) VALUES (106, 'Jennifer');
INSERT INTO employee (id, name) VALUES (107, 'Tim');

-- Insert the links
-- John manages Paul, Simon, Ken
INSERT INTO join_table (manager_id, employee_id) VALUES (1, 101);
INSERT INTO join_table (manager_id, employee_id) VALUES (1, 102);
INSERT INTO join_table (manager_id, employee_id) VALUES (1, 103);
-- Bob manages Paul, Simon, Kevin, Jack
INSERT INTO join_table (manager_id, employee_id) VALUES (2, 101);
INSERT INTO join_table (manager_id, employee_id) VALUES (2, 102);
INSERT INTO join_table (manager_id, employee_id) VALUES (2, 104);
INSERT INTO join_table (manager_id, employee_id) VALUES (2, 105);
-- Mary manages Jennifer, Tim
INSERT INTO join_table (manager_id, employee_id) VALUES (3, 106);
INSERT INTO join_table (manager_id, employee_id) VALUES (3, 107);
-- Sue manages Jennifer, Tim
INSERT INTO join_table (manager_id, employee_id) VALUES (4, 106);
INSERT INTO join_table (manager_id, employee_id) VALUES (4, 107);
-- Alan manages Paul, Simon, Ken, Jennifer, Tim
INSERT INTO join_table (manager_id, employee_id) VALUES (5, 101);
INSERT INTO join_table (manager_id, employee_id) VALUES (5, 102);
INSERT INTO join_table (manager_id, employee_id) VALUES (5, 103);
INSERT INTO join_table (manager_id, employee_id) VALUES (5, 106);
INSERT INTO join_table (manager_id, employee_id) VALUES (5, 107);
-- Mike manages Paul, Simon, Ken
INSERT INTO join_table (manager_id, employee_id) VALUES (6, 101);
INSERT INTO join_table (manager_id, employee_id) VALUES (6, 102);
INSERT INTO join_table (manager_id, employee_id) VALUES (6, 103);

-- For sanity
CREATE UNIQUE INDEX employee_name_uidx ON employee(name);
So if I'm querying for manager John, I want to find the other managers who manage the exact same list of employees.
Answer should be Mike.
If I'm querying for manager Mary, answer should be Sue.

This query will give me the list of managers who manage some of the same employees as John, but not the exact same employees...
SELECT DISTINCT m.name AS manager
FROM manager m, join_table jt, employee e
WHERE m.id = jt.manager_id
AND jt.employee_id = e.id
AND e.id IN (
	SELECT e.id
	FROM manager m, join_table jt, employee e
	WHERE m.id = jt.manager_id
	AND jt.employee_id = e.id
	AND m.name = 'John')
ORDER BY 1;
I thought about using set operations to find managers whose list of employees minus my employees is null and where my employees minus their list of employees is null. But surely there's a simpler more elegant way.
Any ideas?
Btw, I need to run this as a batch job against tables with >20 million rows so query efficiency is key.
This post has been answered by munky on Aug 11 2010
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 8 2010
Added on Aug 11 2010
8 comments
4,309 views