Sql server heirarchical data query return depth of query

jazz__man
jazz__man used Ask the Experts™
on
Hi,

I have a ProductType table and wish to return the maximum depth a product type lies within.

The table design is as follows

ProductTypes
-----------------------------------
ProductTypeCode
ParentProductTypeCode
ProductTypeDescription

Example

Sport>SnookerTables>SixFoot>GreenCloth
Home>PhotoFrames>SilverPlated

The maximum depth will be the green cloth and I would require the depth number returned ie 4 since GreenCloth is at the 4th level

Thanks





Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
here is full details on how to do it:

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

try


;with cte as (
	select producttypecode as rt, producttypecode, parentproductypecode,, producttypedescription, 0 as level from producttypes
	where parentproducttypecode = 0
	union all
	select b.rt, a.producttypecode, a.parentproductypecode, a.producttypedescription, b.level + 1 from producttypes
	inner join cte on a.parentproducttypecode = b.producttypeCode
)
select * from cte a
where a.level = (select max(level) from cte where rt = a.rt)

Open in new window

You can try this and tweak as needed.

WITH myCte (ProductTypeCode, ProductTypeDescription, Level) AS(
      SELECT a.ProductTypeCode, a.ProductTypeDescription, 1 AS Level
      FROM ProductType a
      WHERE a.ParentProductTypeCode IS NULL
      
      UNION ALL
      
      SELECT b.ProductTypeCode, b.ProductTypeDescription, Level+1
      FROM ProductType b INNER JOIN myCte ON b.ParentProductTypeCode=myCte.ProductTypeCode
)
select * from myCte
some typos there
;with cte as (
	select producttypecode as rt, producttypecode, parentproducttypecode,, producttypedescription, 0 as level from producttypes
	where parentproducttypecode = 0
	union all
	select b.rt, a.producttypecode, a.parentproducttypecode, a.producttypedescription, b.level + 1 from producttypes
	inner join cte on a.parentproducttypecode = b.producttypeCode
)
select producttypecode, parentproducttype, producttypedescription from cte a
where a.level = (select max(level) from cte where rt = a.rt)

Open in new window

Author

Commented:
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "a.parentproducttypecode" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "b.producttypeCode" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "b.rt" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "a.producttypecode" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "a.parentproductypecode" could not be bound.
Msg 4104, Level 16, State 1, Line 12
The multi-part identifier "a.producttypedescription" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "b.level" could not be bound.
missed the alias there
;with cte as (
	select producttypecode as rt, producttypecode, parentproducttypecode,, producttypedescription, 0 as level from producttypes
	where parentproducttypecode = 0
	union all
	select b.rt, a.producttypecode, a.parentproducttypecode, a.producttypedescription, b.level + 1 from producttypes a
	inner join cte b on a.parentproducttypecode = b.producttypeCode
)
select producttypecode, parentproducttype, producttypedescription from cte a
where a.level = (select max(level) from cte where rt = a.rt)

Open in new window

Author

Commented:
Excellent
Hello?

Did you check my query?
LIONKING query is incomplete, it will not return this part,

>>The maximum depth will be the green cloth and I would require the depth number returned ie 4 since GreenCloth is at the 4th level<<

Can you please advise?


Author

Commented:
ralmada,

I gave LIONKING the points since the answer was presented so neatly and gave me the framework for exactly what I needed. I did have to make one small adjustment to select the max level but this change was really simple as the clever work had been done. Perhaps on reflection you deserved some of the points, if this is how you feel then sorry. I did check your solution and it returned an error....

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Author

Commented:

For info, I changed the very last line of code to this....

select MAX(level)MaxLevel from myCte

so as you can see, it was a very simple adjustment.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial