Recursive query in oracle 9i

[Pritesh]

Thanks jkoopman for the prompt reply-You're right I should have posted some sample date to make things clearer.Here's my edited question..

Supposing this is my table:

emp_id emp_Lname job_title
s000230 Brown Programmer
s000780 Brown Accountant
s000340 Smith Systems Analyst
s000580 Smith General Support

Now I want to list the emp_id and job_title of all employees with the same surname

I understand I could do this with a simple query such as
Select emp_id,job_title
from employee
where emp_Lname='Brown'

But what if my database has potentially hundreds of employees with matching surnames? Such as I have displayed 2 such records here Brown and Smith.

I attempted to write a query somewhat like this:

Select A.emp_id,A.emp_Lname,A.job_title
from employee A, employee A
where A.emp_Lname=A.emp_Lname

This query didn't work-I know it looks a bit stupid but I am trying!

Supposing I had the following table named emp

emp_num emp_Lname emp_Mnger
100 Kolmycz
101 Lewis 100
102 Vandam 100

Now if I want to generate a list of all employees with their managers' names then the following query works:

SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MNGER,
B.EMP_LNAME
FROM EMP A, EMP B,
WHERE A.EMP_MNGER=B.EMP_NUM
ORDER BY A.EMP_MNGER

Couldn't I write a query along similar lines for my first question where I want to list the emp_id and job_title for all employees with the same surname?

I hope my question is clearer...Your help would be much appreciated...I was hoping to come up with a solution in preparation for my test in 2 days!

Thanks again!

[jkoopmann]

i think you will need to re-define what it is you are asking. recursive usually implies that there is a self referencing relationship of some form. if you are just trying to get all rows that have the same surname than that is just an equality condition. where a simple sql statement like the following would suffice.
select * from employee where surname = 'SMITH';

if there is a relationship that needs to be imposed upon this then we will need to know the table definition and what the relationship that you are trying to come up with.

alot of times it is good to give sample data to show what it is you are trying to accomplish.

let us know.

[jkoopmann]

the problem you are having is because there is no columns in the first query that you can actually do recursive sql on.

unlike the second example there is a manager row (emp_num= 100) that the other employees have as a manager (via the emp_Mnger=100).

so, in order to produce something meaningfull in your first query, you will need to introduce a row / column in your test data that other rows can reference and thus be able to produce a recursive relationship.

FYI.
the reason your first query doesn't work is because you can not have two tables in the from clause that are given a rename that is the same. you would need to do something similar as the following: just beware that the result will just be a cartesian product of all employees that have the same last name.
Select A.emp_id,A.emp_Lname,A.job_title
from employee A, employee B
where A.emp_Lname=B.emp_Lname

good luck on your test.

[Pritesh]

Thanks so much for your expert opinion..I now understand that I was trying to do a self-join within the same column and therefore the difficulty and you're right the closest solution being
SELECT A.emp_id,A.emp_Lname,A.job_title
FROM employee A, employee B
WHERE A.emp_Lname=B.emp_Lname

gives a caretesian product of all employees who the same surname...Thanks again for clarifying...

This forum is great and I hope to return in the future with sensible questions.