SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Populating

  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    South Africa
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Populating

    I have a serious problem of populating a table with a hierachy in it. Say table name is "Offices" both in an operational database and in a warehouse.

    Operational "Office":
    office_num office_name office_type parent_office
    101 Connecticut B 510
    234 New York B 550
    245 Massachussetts B 550
    910 North East R 900
    335 Vermont B 520
    501 Acct-501 A 910
    550 Acct-550 A 910
    520 Acct-520 A 910

    Warehose "Office":
    office_num Region Accounting Business

    It's said at the lowest level there is a Business office which belongs to an Accounting office which belongs to a Regional office
    Regional office = 910 = North East
    Question is: for each office in the warehouse, must contain the name of the Accounting office it belongs to, the name of the Region it belongs to, and the name of the Business

    Please somebody help me

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    run this, it has a few additional columns that you can remove/rename as necessary
    Code:
    select B.office_num   as Bnum 
         , B.office_name  as Bname  
         , B.office_type  as Btype  
         , A.office_num   as Anum  
         , A.office_name  as Aname  
         , A.office_type  as Atype  
         , R.office_num   as Rnum 
         , R.office_name  as Rname  
         , R.office_type  as Rtype  
      from Office B
    inner
      join Office A
        on B.parent_office
         = A.office_num       
    inner
      join Office R
        on A.parent_office
         = R.office_num
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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