Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    2

    Unanswered: recursive querry question

    Hello Forum,

    I'm using SQL Server 2005. I hope someone here can help me with my SQL statement.
    I have a table with assets where astno is the asset number and mstno is the master asset number.
    One asset can be linked to another asset. If that happens, all that changes in the database is that the master asset number is entered under mstno for that asset.
    In my table I have a couple of assets linked to a master asset, and this master asset again linked to another master asset.
    This double-link is where I have problems with my statement.

    What I'd like to achieve is have the asset listed from top to down based on their hierarchy.


    My table looks like this:

    astno | mstno
    ---------|----------
    SIT-0003 |
    SIT-0002 |
    CAM-0002 | SIT-0003
    LIV-0009 | SIT-0003
    RIG-0014 | SIT-0003
    LIV-0019 | RIG-0014

    RIG-0002 | SIT-0002
    LIV-0008 | RIG-0002
    CAM-0001 | RIG-0002
    TRU-0019 | RIG-0002



    My querry looks like this:
    Code:
    USE [cap100]
    go
    WITH Sites (mstno, astno)
    AS
    (
        -- Anchor member definition
        SELECT mstno, astno FROM AMASST 
        UNION ALL
    
        -- Recursive member definition
        SELECT c.mstno, c.astno From amasst as c
        inner JOIN Sites as p
        ON c.astno = p.mstno
    )
    
    -- Statement that executes the CTE
    SELECT distinct mstno, astno FROM Sites where mstno <> '' order by mstno desc
    GO
    The result I get is:
    SIT-0003 | CAM-0002
    SIT-0003 | LIV-0009
    SIT-0003 | RIG-0014
    SIT-0002 | RIG-0002
    RIG-0014 | LIV-0019
    RIG-0002 | CAM-0001
    RIG-0002 | LIV-0008
    RIG-0002 | TRU-0019


    The result I'm aiming for is:
    SIT-0003 | CAM-0002
    SIT-0003 | LIV-0009
    SIT-0003 | RIG-0014
    ---- | LIV-0019
    SIT-0002 | RIG-0002
    ---- | CAM-0001
    ---- | LIV-0008
    ---- | TRU-0019

    Basically the assets which are linked through a RIG to SIT should appear as they were linked to SIT directly.

    Any help much appreciated!

  2. #2
    Join Date
    May 2010
    Posts
    2
    This one works,
    thanks.

    Code:
    WITH Sites (mstno, astno, rootasst)
    AS
    (
        -- Anchor member definition
        SELECT mstno, astno ,mstno as RootAsst
        FROM AMASST where mstno <>''
        UNION ALL
    
        -- Recursive member definition
        SELECT c.mstno, c.astno,p.rootasst From amasst as c
        inner JOIN Sites as p
        ON p.astno = c.mstno
    )
    
    -- Statement that executes the CTE
    SELECT * from Sites order by rootasst desc

Posting Permissions

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