Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Tampa, FL
    Posts
    1

    Cool Unanswered: help with stored procedure

    Hello all! I am new to stored procedures and I hope you guys can help me with this one. I am using MS SQL Server 2000.

    I have three talbes with the following structures:

    company_info:
    company_id (primary key)
    company_name
    (more fields not needed in proc)

    company_relations (junction table):
    relation_id (primary key)
    parent_company_id (foreign key = company_t.company_t_id)
    child_company (foreign key = company_t.company_t_id)

    company_t:
    company_t_id (primary key)
    company_id (foreign key = company_info.company_id)

    The company_relations table contains all of the companies that my client does business with regardless of their capacity. As a result there are many parent/child/grandchild relationships on this table. There is no limit as to how many generations deep these relationships can go. For example, one parent (1st level company) can have 10 children (2nd level), and those 10 children can have 15 subsidieries, who can also have subsidieries, and so on.

    All of my reporting must be done by parent company and contain breakouts for each child company, grandchild company and so on. So, I would like to create a procedure that builds a single table containing all of these relationships. Make sense?

    Thank you in advance for all of your help. Eventhough this is my first post, I have been reading for some time and have learned a lot from you guys!

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: help with stored procedure

    RE:

    Q1 ... There is no limit as to how many generations deep these relationships can go. For example, one parent (1st level company) can have 10 children (2nd level), and those 10 children can have 15 subsidieries, who can also have subsidieries, and so on. All of my reporting must be done by parent company and contain breakouts for each child company, grandchild company and so on. So, I would like to create a procedure that builds a single table containing all of these relationships. Make sense?
    A1 Maybe.

    The description sounds like the requirement amounts to logically modeling a hierarchal system (which, to a point, may be done reasonably well using MS Sql Server).

    You may wish to consider implementing a Maps and Sets table approach to logically model such a hierarchal system.

    Using a Maps and Sets implementation approach one may generate a hierarchal 'explosion' result set from a select statement given a specific SetID. (For example, to return a specific (sub set) result set for all 'sub-companies' hierarchaly below or within the control of a setID that represents a desired 'company' of interest.)

    Example Maps and Sets schemas:
    TABLE [Sets] (
    [SetID],
    [SetName])

    TABLE [Maps] (
    [MapID],
    [SetID],
    [SubID])

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Question Re: help with stored procedure

    Question I
    If this interpretation of the requirement is correct?? Then:

    One may use something like the following (within a stored procedure) to return a result set constituting "a single table containing all of these relationships". (One might also consider including a Where to restrict the result set to SubSetIDs assigned as subordinate to the SetID of a specific company of interest.)

    SELECT
    dbo.Sets.SetName AS SetName,
    dbo.Sets.SetID As SetID,
    Sets_1.SetName AS SubSetName,
    dbo.Maps.SubID As SubSetID
    FROM
    dbo.Maps
    INNER JOIN
    dbo.Sets
    ON
    dbo.Maps.SetID = dbo.Sets.SetID
    INNER JOIN
    dbo.Sets Sets_1
    ON
    dbo.Maps.SubID = Sets_1.SetID

Posting Permissions

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