Results 1 to 2 of 2

Thread: Recursive query in oracle 9i

  1. #1
    Join Date
    Oct 2003
    Location
    In the Fiji Islands
    Posts
    3

    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!

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    i have replied to this in the expert forum.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •