Grandchildren example?

Question

Post reply

Grandchildren example?

mhildner

Valued Member

Points: 64
More actions
April 29, 2008 at 8:25 am

#184930

Greetings,
I'm suprised my searching has not yet come up with anything. I need to create a report that shows a parent/children/grandchildren setup. For example:
Customer: John Smith
Invoice 101
Hammer
Screw driver
Invoice 102
Box of Nails
Paint
I don't have a problem showing the parent, or child items (with a subreport), but I cannot figure out how to list the grandchildren.
If anyone can help me out or point me to an example I'd much appreciate it.
Thanks,
Mike

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's a standard hierarchy situation.

There are two basic ways to store and query those: Adjacency, and Nested Sets.

Adjacency is better at dealing with data that changes a lot. Nested Sets is better with relatively static data.

For adjacency, store an ID and a ParentID in the table, and use a CTE in your underlying query to get the data. Look up Recursive CTEs in Books Online for how to set this up and use it.

For Nested Sets, Joe Celko has the best data on that in his SQL for Smarties book. In summary, you give each level of the hierarchy a start number and and end number, and each level below that has start and end numbers that are inside that range. E.g.: Hammer (start 1, end 6), head (start 2, end 3), handle (start 4 end 5). This means you can query "where start between 1 and 6", and you'll get everything related to hammers, no matter how many levels deep it needs to go.

Nested sets are very, very fast to select. They are, on the other hand, a bit of a pain to update/insert.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Can you give some example data and table structure(s)?

Are you returning all the data needed in the report in one data set or multiple data sets? What does the data look like coming into the report? Typically in SSRS you can just use grouping in a table to show levels like:

Customers -> Orders -> Order Details -> Product Details

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

mhildner Valued Member Points: 64 More actions · Answer 3

Thanks for the replies. All my data is in one dataset. Sounds like grouping will work for me. Got pulled away on another project so haven't been able to try it yet.

Thanks,

Mike