# Related Sites > SQL Course >  Recursive query in oracle 9i

## Pritesh

I am learning the grassroots of sql..am using oracle 9i personal edition...would much appreciate if someone could provide some feedback on my 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?

Am trying to come up with a solution in preparation for my test in 2 days!! Would appreciate some help!
Thanks!

----------


## jkoopmann

i have replied to this in the expert forum.

http://forums.databasejournal.com/sh...3231#post83231

----------

