Infrastructure Software

Hi,

See this thread:
4478137

Actually, silly duplication there...

WITH manager_list AS
(
 SELECT name,
        LTRIM(MAX(SYS_CONNECT_BY_PATH(id,','))
        KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
 FROM   (SELECT m.name,
                e.id,
                ROW_NUMBER() OVER (PARTITION BY m.name ORDER BY e.id) AS curr,
                ROW_NUMBER() OVER (PARTITION BY m.name ORDER BY e.id) -1 AS prev
         FROM   manager m, 
                join_table jt, 
                employee e
  WHERE m.id           = jt.manager_id 
  AND   jt.employee_id = e.id)
  GROUP BY name
  CONNECT BY prev = PRIOR curr AND name = PRIOR name
  START WITH curr = 1
)
SELECT a.*
FROM   manager_list m,
       manager_list a
WHERE  m.employees = a.employees
AND    m.name      = :P_MANAGER

Cheers

Ben

Great, thanks for that.
I had thought about creating an ordered list of employees for the current manager and checking if its the same for any other manager, but I could only think of how to do that in pl/sql.

The straight sql solution is great. Sure it'd be easier on 11g, but the customer is still on 10g, so I'll go with Munky's approach.

Thanks again.

Also, if you just want to display the results without the input manager as well, just add...

AND    a.name      != :P_MANAGER

To the bottom of the above query.

Cheers

Ben

Ok, cool.
I'll try out this approach this evening on the larger tables I need to run it on to see how it scales.

Make sure you use the second version of the query I posted.

Cheers

Ben

similar this thread 4478137

This is my DB2 solution which is like solution of Frank Kulash ;-)
http://www.geocities.jp/oraclesqlpuzzle/db2-sql2-tatujin1.html#2-8

with manager (id, name) as(
select 1, 'John' from dual union
select 2, 'Bob'  from dual union
select 3, 'Mary' from dual union
select 4, 'Sue'  from dual union
select 5, 'Alan' from dual union
select 6, 'Mike' from dual),
join_table (manager_id, employee_id) as(
select 1, 101 from dual union
select 1, 102 from dual union
select 1, 103 from dual union
select 2, 101 from dual union
select 2, 102 from dual union
select 2, 104 from dual union
select 2, 105 from dual union
select 3, 106 from dual union
select 3, 107 from dual union
select 4, 106 from dual union
select 4, 107 from dual union
select 5, 101 from dual union
select 5, 102 from dual union
select 5, 103 from dual union
select 5, 106 from dual union
select 5, 107 from dual union
select 6, 101 from dual union
select 6, 102 from dual union
select 6, 103 from dual),
tmp as(
select a.name,a.ID,b.employee_id,
count(*) over(partition by a.ID) as cnt
  from manager a Join join_table b
    on a.id= b.manager_id)
select b.name
  from tmp a,tmp b
 where a.cnt = b.cnt
   and a.name = 'John'
   and a.name != b.name
   and a.employee_id = b.employee_id
group by b.id,b.name,a.cnt
having count(*) = a.cnt;

NAME
----
Mike