May 3, 2010 at 7:40 am
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:
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!
May 3, 2010 at 7:53 am
Sorry , im a bit lost by your requirements can you post a simpler example ( I wont be offended if you feel its to simple and treat me like a child :))
May 3, 2010 at 8:09 am
Hi Dave,
thank you for your quick reply.
This is my table:
Asset | Masterasset
----------------------|
Asset1| Masterasset1
Asset2| Masterasset1
Asset3| Asset2
Asset4| Asset2
In the result I want to show the hierarchie of the assets.
Asset | Masterasset
----------------------|
Asset1| Masterasset1
Asset2|
Asset3|
Asset4|
Asset2 and Asset3 aren't linked to Masterasset1 directly. They are linked to Asset2 and Asset2 is linked to Masterasset1.
But I only want to know which assets and 'sub-assets' are linked to a masterasset.
Hope this is a bit easier to understand. 😉
May 3, 2010 at 8:19 am
Why isn't Asset2 linked to MasterAsset1 in the result? What you've posted doesn't make sense.
May 3, 2010 at 8:26 am
Hi Steve,
I meant to show a hierarchie there.
You are right. They should of course all be linked to Masterasset1. I just didn't want to show Masterasset1 repeatedly.
That's the result I need.
Asset | Masterasset
-----------|------------
Asset1| Masterasset1
Asset2| Masterasset1
Asset3| Masterasset1
Asset4| Masterasset1
May 3, 2010 at 8:31 am
I think your confusion comes from not defining your anchor element(s) correctly
USE [cap100]
go
WITH Sites (mstno, astno,rootasst)
AS
(
-- Anchor member definition
SELECT mstno, astno ,astno as RootAsst
FROM AMASST where Astno = 'Masterasset1'
UNION ALL
-- Recursive member definition
SELECT c.mstno, c.astno,p.rootasst From amasst as c
inner JOIN Sites as p
ON c.astno = p.mstno
)
-- Statement that executes the CTE
SELECT * from Sites
May 3, 2010 at 8:58 am
Hi Dave,
thanks for your reply.
Can't get it to work with your code either.
It does not list me the assets which aren't linked to Masterasset1 directly.
It will only go down 1 level. So I'm missing Asset3 and Asset4.
Just tried an SQL statement but it has the same problem, only goes down to the 1st level. 🙁
SELECT AMASST.MSTNO, AMASST.ASTNO
FROM AMASST AMASST INNER JOIN AMASST AMASST_1 ON AMASST.MSTNO=AMASST_1.ASTNO
May 3, 2010 at 9:10 am
The join in the recursive element was reversed
with AMASST
as
(
Select 'Asset1' as Astno,
'Masterasset1' as mstno
union all
Select 'Asset2','Masterasset1'
union all
select 'Asset3','Asset2'
union all
select 'Asset4','Asset2'
)
,Sites (mstno, astno,rootasst)
AS
(
-- Anchor member definition
SELECT mstno, astno ,mstno as RootAsst
FROM AMASST where mstno = 'Masterasset1'
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
May 3, 2010 at 9:29 am
What's your name? CHAMPION!
Awsome, now I finally got it. Thanks so much!
Out of interest: Can I only do this with CTE or can this be done in a normal SQL statement as well - like the one a couple of posts ago?
Thanks again!
May 3, 2010 at 9:40 am
Not really, using a CTE is the only single statement way to traverse a hierarchy.
If you want to limit your self to X levels , you can simply left join the table to itself x times.
There are other methods , but IMO there is no significant advantage over a recursive CTE.
May 3, 2010 at 7:15 pm
Thanks Dave!
Great help!
May 3, 2010 at 9:33 pm
D'oh, sorry to bring this up again. I thought I had it.
I'm trying to get the description for ROOTASST. Thought that's straight forward but its given me the wrong description under 'MasterDesc'.
Do I need to add another Join just for the description? That doesn't seem right...
WITH Sites(MSTNO, ASTNO, [DESC], ACQDATE, ROOTASST, MasterDesc) AS
(SELECT MSTNO, ASTNO, [DESC], ACQDATE, MSTNO AS RootAsst, [DESC] AS MasterDesc
FROM dbo.AMASST
WHERE (MSTNO <> '')
UNION ALL
SELECT c.MSTNO, c.ASTNO, c.[DESC], c.ACQDATE, p.RootAsst, p.MasterDesc
FROM dbo.AMASST AS c INNER JOIN Sites AS p ON p.astno = c.MSTNO)
SELECT * FROM Sites AS Sites_1
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply