How can I get a distinct list of elements in a hierarchical query?

Posted by RenderIn on Stack Overflow See other posts from Stack Overflow or by RenderIn
Published on 2010-05-20T05:23:28Z Indexed on 2010/05/20 5:30 UTC
Read the original article Hit count: 401

I have a database table, with people identified by a name, a job and a city. I have a second table that contains a hierarchical representation of every job in the company in every city.

Suppose I have 3 people in the people table:

[name(PK),title,city]
Jim, Salesman, Houston
Jane, Associate Marketer, Chicago
Bill, Cashier, New York

And I have thousands of job type/location combinations in the job table, a sample of which follow. You can see the hierarchical relationship since parent_title is a foreign key to title:

[title,city,pay,parent_title]
Salesman, Houston, $50000, CEO
Cashier, Houston, $25000
CEO, USA, $1000000
Associate Marketer, Chicago, $75000
Senior Marketer, Chicago, $125000

.....

The problem I'm having is that my Person table is a composite key, so I don't know how to structure the start with part of my query so that it starts with each of the three jobs in the cities I specified.

I can execute three separate queries to get what I want, but this doesn't scale well. e.g.:

select * from jobs
start with city = (select city from people where name = 'Bill') and title = (select title from people where name = 'Bill')
connect by prior parent_title = title
UNION
select * from jobs
start with city = (select city from people where name = 'Jim') and title = (select title from people where name = 'Jim')
connect by prior parent_title = title
UNION
select * from jobs
start with city = (select city from people where name = 'Jane') and title = (select title from people where name = 'Jane')
connect by prior parent_title = title

How else can I get a distinct list (or I could wrap it with a distinct if not possible) of all the jobs which are above the three people I specified?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about hierarchical-query