order SQL querry

[johnhugh]

Hello,

I have 1 table which contains asset information.
An asset can be linked to 1 other asset making one the assets a master asset and the other the component asset.
A master asset can be linked to another master asset.

I now want to create a SQL statement which shows me the hierarchy of assets.

I have the following statement:

SELECT "AMASST"."MSTNO" as MasterNumber, "AMASST_1"."DESC" as MasterDesc, "AMASST"."ASTNO" as ComponentNumber, "AMASST"."DESC" AS ComponentDesc
 FROM   "cap100"."dbo"."AMASST" "AMASST" INNER JOIN "cap100"."dbo"."AMASST" "AMASST_1" ON "AMASST"."MSTNO"="AMASST_1"."ASTNO"

This gives me this result:
MasterNumber | MasterDesc | ComonentNumber | ComponentDesc
SIT-0003 | site | CAM-0001 | cam1
SIT-0003 | site | RIG-0002 | rig
RIG-0002 | rig | CAM-0002 | cam2

The result I'd like to see shows the hierarchy of assets.
MasterNumber | MasterDesc | ComonentNumber | ComponentDesc
SIT-0003 | site | CAM-0001 | cam1
SIT-0003 | site | RIG-0002 | rig
----- | ------- | CAM-0002 | cam2

All assets have SIT-0003 as the master asset. They are linked to it either directly (CAM-0001 and RIG-0002) or they are linked to it through another asset (CAM-0002 is linked to SIT-0003 through RIG-0002)

Would anyone be able to tell me whether I can achieve this with SQL and how I have to alter my querry?
Any help much appreciated.

 

[bborissov]

What version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[johnhugh]

Sorry, forgot about that.
SQL 2005

 

[bborissov]

Check:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[johnhugh]

Hi Borislav,

thanks for your reply.
Recursive querries gives me exactly the same result I have already.

My querry looks like this:

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 [/color red]
SIT-0002 | RIG-0002[/color red]
RIG-0014[/color red] | LIV-0019
RIG-0002[/color red] | CAM-0001
RIG-0002[/color red] | LIV-0008
RIG-0002[/color red] | TRU-0019


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



In my table it looks like this:

astno | mstno
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

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

 

[johnhugh]

This one works,
thanks.

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