Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2001
    Posts
    27

    Unanswered: Can a recursive query do this?

    I am wondering if there is some type of recursive query to return the values I want from the following database.

    Here is the setup:

    The client builds reptile cages.

    Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

    The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

    PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

    Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

    Here is a quick schema:

    Table: PRODUCT
    --------------------------
    PRODUCTID PK
    PRODUCTNAME nVarChar(30)


    Table: ASSEMBLY
    --------------------------
    PRODUCTID PK (FK to PRODUCT.PRODUCTID)
    COMPONENTID PK (FK to PRODUCT.PRODUCTID)
    QTY INT


    I can write a query that takes the PRODUCTID, and returns all



    PRODUCT
    =======
    PRODUCTID PRODUCTNAME
    --------- -----------
    1 Cage Assembly - Solid Sides
    2 Cage Assembly - Split Back
    3 Cage Assembly - Split Sides
    4 Cage Assembly - Split Top/Bottom
    5 Cage Assembly - Split Back and Sides
    6 Cage Assembly - Split Back and Top/Bottom
    7 Cage Assembly - Split Back and Sides and Top/Bottom
    8 33S - Aluminum Divider
    9 33C - Aluminum Frame
    10 T3C - Door Frame
    11 Connector Kit
    12 Connector Socket
    13 Connector Screws



    ASSEMBLY
    =========
    PRODUCTID COMPONENT QTY
    --------- --------- ---
    1 9 8
    1 10 4
    1 11 1
    2 1 1
    2 8 1
    3 1 1
    3 8 1
    4 1 1
    4 8 1
    5 1 1
    5 8 2
    6 1 1
    6 8 2
    7 1 1
    7 8 3
    11 12 8
    11 13 8



    I need a query that will give me all parts for each PRODUCT.

    Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

    The results would be:


    PRODUCTID PRODUCTNAME
    --------- -----------
    2 Cage Assembly - Split Back
    1 Cage Assemble - Solid Back
    9 33C - Aluminum Frame
    10 T3C - Door Frame
    11 Connector Kit
    8 33S - Aluminum Divider
    12 Connector Socket
    13 Connector Screws

    Is it possible to write such a query or stored procedure?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in a specific case, yes, if you know in advance how many levels down the hierarchy of assemblies/parts you need to go, you would write a left outer join query with as many joins as the maximum number levels you need to traverse to find all component parts for the given part

    in the general case, where this number of levels is not known in advance, no, you can't write a query for this

    however, you could write a stored proc, but note that the stored proc would be running a query inside a loop and building up its results in a temp table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2001
    Posts
    27

    A UDF solution

    Here is a solution that uses a UDF. I thought it was quite slick.

    http://www.sqlservercentral.com/foru...ssageid=152361

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, that's what i suggested -- a query inside a loop that builds a temp table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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