Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: Query help - self joined table

    We have a hierarchy table that one keep navigating to get to the top (or bottom) of employee hierarchies.

    Originally, this query was written by our DBA (no longer available), but he didn't seem to use left joins. The following will execute, but it is giving me the wrong results.

    Code:
      SELECT c.partysitenumber_pk PartySiteNumber, c.accountnumber AccountNumber, c.name Company,
             tm.territoryname Territory, tm.TERRITORYID_PK TerritoryID, r.SALESREPID_FK SalesRepID, sh.SALESREP_NAME VP, sh.GROUP_NAME BusinessUnit, sh.Branch Branch        
        FROM tbl_customer_all c, tbl_territory_master tm, tbl_territory_rep_master r, TBL_I98_SALES_HIER_V sh, tbl_status s
       WHERE tm.TERRITORYID_PK=c.ASSTERRID_FK
         AND c.partysitenumber_pk = s.partysitenumber_pk AND c.assterrid_fk = s.assterrid_fk AND c.accountnumber = s.accountnumber AND s.status = 'M'
         AND r.TERRITORYID_FK=tm.TERRITORYID_PK
         AND tm.territoryname <> 'CATCHALL'
         AND sh.GROUP_ID = ( SELECT DISTINCT group_id
                               FROM TBL_I98_SALES_HIER_v
                              WHERE LEVEL=(SELECT MAX(LEVEL) - 1
                                             FROM TBL_I98_SALES_HIER_v
                                       START WITH group_id=r.GROUPID
                                       CONNECT BY GROUP_id = PRIOR PARENT_GROUP_id)
                         START WITH group_id=r.GROUPID
                         CONNECT BY GROUP_id = PRIOR PARENT_GROUP_id)
    I would like to use the following format, but I can't figure out how to get the self joining table involved. The following does NOT run. I am hoping to join the tbl_I98_sales_hierarchy by starting at the bottom (of the hierarchy) with the group_id equal to the tbl_territory_rep_master.group_ID, and then traversing up to the next to highest level and pulling results from that row. Does this make sense?

    Code:
        SELECT DISTINCT c.partysitenumber_pk PartySiteNumber, c.accountnumber AccountNumber, c.name Company,
               tm.territoryname Territory, tm.TERRITORYID_PK TerritoryID, r.SALESREPID_FK SalesRepID--, sh.SALESREP_NAME VP, sh.GROUP_NAME BusinessUnit, sh.Branch Branch
          FROM tbl_status s
     LEFT JOIN tbl_customer_all c ON c.partysitenumber_pk = s.partysitenumber_pk AND c.assterrid_fk = s.assterrid_fk AND c.accountnumber = s.accountnumber
     LEFT JOIN tbl_territory_master tm ON tm.TERRITORYID_PK=c.ASSTERRID_FK
     LEFT JOIN tbl_territory_rep_master r ON r.TERRITORYID_FK=tm.TERRITORYID_PK
     LEFT JOIN TBL_I98_SALES_HIER_V sh ON sh.group_id = ( SELECT DISTINCT group_id
                                                                  FROM TBL_I98_SALES_HIER_v
                                                                 WHERE LEVEL=(SELECT MAX(LEVEL) - 1
                                                                                FROM TBL_I98_SALES_HIER_v
                                                                          START WITH group_id=r.GROUPID
                                                                          CONNECT BY GROUP_id = PRIOR PARENT_GROUP_id)
                                                            START WITH group_id=r.GROUPID
                                                            CONNECT BY GROUP_id = PRIOR PARENT_GROUP_id) 
         WHERE s.status = 'M'
    Can someone point me in the appropriate direction to use a LEFT JOIN statement on a self joining table?

    Thanks,

    Rob
    Last edited by rbb; 09-13-07 at 13:52.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the "write" direction LOL

    this is classic: "Orignally, this query was written by our DBA (no longer available), but he didn't seem to use left joins."

    i am really sorry, i must apologize

    i do not intend to make fun of your situation, but i think you need to hire a new DBA
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    Quote Originally Posted by r937
    the "write" direction LOL

    this is classic: "Orignally, this query was written by our DBA (no longer available), but he didn't seem to use left joins."

    i am really sorry, i must apologize

    i do not intend to make fun of your situation, but i think you need to hire a new DBA
    I apologize for my poor editing - I was concentrating on the SQL. Can you please let me know if this left join is even possible?

    Or should I look into writing a stored procedure that can accept the starting group id, and returns the appropriate parent group_id?

    Thanks,

    Rob

  4. #4
    Join Date
    Sep 2003
    Posts
    30
    Got it!

    I ended up create a function that accepts the starting group_id, and returns the correct parent group_id.

Posting Permissions

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