-
Recursive query in oracle 9i
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!
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|