March 15, 2010 at 6:18 am
Hello,
I am new to SQL Server and really struggling with the following scenario.
The following query returns for me the proper root folder, child folder and child folder contents;
[p]select root.projname as [Root Folder],
sub.projname as [Sub Folder],
docs.docname as [Documents]
from dbo.project root
inner join dbo.project sub
on root.projid = sub.projpid
left join dbo.project_content items
on items.projid = sub.projid
left join dbo.content docs
on items.item_id = docs.docnum
where root.projid = 370606.0
order by sub.projname asc[/p]
Root FolderSub FolderDocuments
Root Folder 1Child Subfolder 1Document
Root Folder 1Child Subfolder 1Document
Root Folder 1Child Subfolder 2Document
Root Folder 1Child Subfolder 2Document
Root Folder 1Child Subfolder 3Document
Root Folder 1Child Subfolder 4Document
However, these results are limited to 1 row, and does not traverse the table hierarchy to find all child objects within the tree. What I am hoping to achieve would look like this;
Root FolderSub FolderChild DocumentsSub-Sub FolderGrandchild DocumentsSub-Sub-Sub FolderGreat-GrandChild Documents
Root Folder 1Child SubfolderDocumentNULLNULLNULLNULL
Root Folder 1Child SubfolderDocumentNULLNULLNULLNULL
Root Folder 2Child SubfolderNULLGrandchild SubfolderDocumentNULLNULL
Root Folder 2Child SubfolderNULLGrandchild SubfolderDocumentNULLNULL
Root Folder 3Child SubfolderDocumentGrandchild SubfolderDocumentNULLNULL
Root Folder 4Child SubfolderNULLGrandchild SubfolderNULLGrtGrandchild SubfolderDocument.
The example I was shown of a recursive query looked like this;
;With CTE(projid,
projpid,
ProjName,
ProjOwner,
DocName)
AS
(
SELECT p.projid,
p.projpid,
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
WHERE p.ProjPID IS NULL
UNION ALL
SELECT p.projid,
p.projpid,
p.ProjName,
p.ProjOwner,
d.DocName
FROM dbo.project p
INNER JOIN dbo.project_content pc
ON pc.ProjTree = p.ProjTree
INNER JOIN dbo.document d
ON d.DocNum = pc.Item_ID
INNER JOIN CTE c
ON c.projid= p.projpid
)
SELECT projid,
projpid,
ProjName,
ProjOwner,
DocName
FROM CTE
OPTION (MAXRECURSION 0)
But the results of this query actually skip any child and grandchild objects. I've been trying to debug it for 3 days without success.
Can anyone assist me in this regard? I'm starting to believe it just isn't possible :unsure:
March 16, 2010 at 7:59 am
You are never getting the child project id so you are joining on the same project id in the recursive part of the CTE. Your query should look more like this (I think):
;
With CTE(projid, projpid, ProjName, ProjOwner, DocName)
AS (
SELECT
ROOT.projid AS root_id,
root.projname as [Root Folder],
sub.projid AS sub_id,
sub.projname as [Sub Folder],
docs.docname as [Documents]
from
dbo.project root inner join
dbo.project sub
on root.projid = sub.projpid left join
dbo.project_content items
on items.projid = sub.projid left join
dbo.content docs
on items.item_id = docs.docnum
where
root.projid IS NULL
UNION ALL
SELECT
ROOT.projid AS root_id,
root.projname as [Root Folder],
sub.projid AS sub_id,
sub.projname as [Sub Folder],
docs.docname as [Documents]
FROM
CTE root INNER Join
dbo.project sub ON
root.sub_Id = sub.projid INNER JOIN
dbo.project_content items
ON sub.projid = items.projid INNER JOIN
dbo.document docs
ON docs.DocNum = items.Item_ID
)
SELECT
root_id,
[root folder]
sub_id,
,
Documents
FROM
CTE
OPTION
(MAXRECURSION 0)
If you provide table structures, sample data, and desired outcome as outlined in the article in the top line of my signature it would be easier to supply a tested and working solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply