DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need help with query

  1. #1
    Join Date
    Jun 2017
    Posts
    1

    Need help with query

    I am trying to do self join on table to find desired result.
    The issue here is there are 7 level of hierarchy and i have to do 7 self joins to get result. But it is taking tie.
    Is there any way we can use other method to resolve it.
    QUERY:-
    Code:
    SELECT DISTINCT
            LOWER(G.parent) AS PARENT_NAME,
            A.ID
          FROM
            EMP a
          LEFT JOIN EMP B
          ON
            a.PARENT_ID = B.EMP_ID
          LEFT JOIN EMP C
          ON
            B.PARENT_ID = C.EMP_ID
          LEFT JOIN EMP D
          ON
            C.PARENT_ID = D.EMP_ID
            LEFT JOIN EMP E
          ON
            D.PARENT_ID = E.EMP_ID
            LEFT JOIN EMP F
          ON
            E.PARENT_ID = F.EMP_ID
            LEFT JOIN EMP G
          ON
            F.PARENT_ID = G.EMP_ID

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Try writing a Hierarchical Query. It is designed for self joining tables. You can tell it to not get into an infinite loop with nocycle. If some of the joins join 7 times and others join more or less than 7 times it will still work. Since you did not hard code each join. If the data is relatively static, then you can create a materialized view that will greatly speed up the query. You need to be on at least 11gR2. I wrote a Hierarchical Query for getting the patch order for sql ids where one of the columns was a colon delimited list of sql ids.

    https://docs.oracle.com/cd/B28359_01...htm#SQLRF52315

Posting Permissions

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


Click Here to Expand Forum to Full Width