Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Location
    UK
    Posts
    4

    Red face Unanswered: A horrible (well, I think so) Recursive Join...

    Hi,

    I have been trying to solve this issue for around 8 hours, now. Can someoone help me?

    I have two tables:

    Employee (
    EMPLOYEE_ID INT PRIMARY KEY,
    NAME VARCHAR(25)
    )

    E.G.:

    1 Mark
    2 Tracey
    3 Jim

    STRUCTURE (
    SUPERVISOR_ID INT,
    SUBORDINATE_ID INT
    )

    E.G.:

    1 3
    2 3

    I need to get a query that gives me the following query:

    SUPERVISOR_ID, SUPERVISOR_NAME, SUBORDINATE_ID, SUBORDINATE_NAME

    I think that the SQL is going to go recursive and I think that this can be done reasonably easily (sadly, I have struck out so far), but how?

    Thanks in advance,


    QuietLeni

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    I don't think its recursive:
    select Sup.*, Emp.*
    from
    Employee Sup
    left outer join STRUCTURE on
    (SUPERVISOR_ID = Sup.EMPLOYEE_ID)
    left outer join Employee Emp on
    (SUBORDINATE_ID = Emp.EMPLOYEE_ID)

  3. #3
    Join Date
    Apr 2008
    Location
    UK
    Posts
    4

    Talking Ahhhhh...

    Thanks for that, LoztInSpace.

    Is there a neater way of doing that SQL (as I do not want to get any null records)? I noticed that you elected to do a LEFT OUTER JOIN and this gave me a whole load of NULL records, which I excluded with a WHERE clause:

    select Sup.*, Emp.*
    from
    Employee Sup
    left outer join STRUCTURE on
    (SUPERVISOR_ID = Sup.EMPLOYEE_ID)
    left outer join Employee Emp on
    (SUBORDINATE_ID = Emp.EMPLOYEE_ID)
    WHERE Emp.EMPLOYEE_ID IS NOT NULL

    Do I need an outer join at all? I seem to get the same results when I remove this word in each JOIN clause?!

    Thanks Again,


    QuietLeni

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    LEFT JOIN = LEFT OUTER JOIN

    Do you understand why you get the NULLs?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2008
    Location
    UK
    Posts
    4
    georgev,

    I thought that LEFT JOIN = LEFT INNER JOIN?

    Yes, because I was selecting every record in the LEFT table and matching them to the right table and returning NULLs for the records without values.

    There is no way of returning the records on the left only?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Inner Join
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2008
    Location
    UK
    Posts
    4

    Talking Inner Join

    Quote Originally Posted by Brett Kaiser
    Inner Join
    Oh I see!!! So the correct SQL is:

    select Sup.*, Emp.*
    from
    Employee Sup
    inner join STRUCTURE on
    (inner join Employee Emp on
    (SUBORDINATE_ID = Emp.EMPLOYEE_ID)

    THANK YOU!!!!

  8. #8
    Join Date
    Feb 2007
    Posts
    62
    Well to be fair my version could have been correct too
    Nowhere did you say 'except for people with no subordinates', hence the outer joins.

Posting Permissions

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