Mapping more than one table using CTE hierarchy re

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mapping more than one table using CTE hierarchy re

Author

Topic

venkatakondareddy
Starting Member

17 Posts

Posted - 2008-12-23 : 01:56:01

Hi,
in my project I need to map more than one table using CTE Hierarchical recursion. At single table i have done in easily.
When i come to more than one table im getting errors.
My code is given below. If any one knows please try to help me to solve this query..

with Reports(KAssetId, ParentAssetId, AssetId, IssueId, InternalIssueRiskId)
as
(
select Kaon.Asset.AssetId,
Kaon.Asset.ParentAssetId,
Kaon.Issue.AssetId,
Kaon.Issue.IssueId,
Kaon.Issue.InternalIssueRiskId
from Kaon.Asset, Kaon.Issue
union all
select e1.AssetId,
e1.ParentAssetId,
e2.AssetId,
e2.IssueId,
e2.InternalIssueRiskId
from Kaon.Asset e1, Kaon.Issue e2 join Reports r
on r.KAssetId=e1.ParentAssetId
)
select KAssetId, ParentAssetId, AssetId, IssueId, InternalIssueRiskId from Reports
------------------------------------------------------------

Here Kaon.Asset is table name with AssetId(PK), ParentAssetId
and Kaon.Issue table with AssetId(FK), IssueId, InternalIssueRiskId

Regards
-----------
Venkat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:42:45

explain what you want with some sample data rather than giving query. that will help us to help you much quicker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources