Hi John.

Can someone show how to setup table(s) to make querying hierarchical data easier and perhaps share a simple SQL to query it?

What we generally do is store the structural information in a single reflexive table with a PK and an FK that point to the parent record. We also have what we call a "rip field" (because it allows you to rip the data out of the table < s >) that is basically a concatenated string of all the ancestors' PK values separated by some delimiter.

For example:

GreatGrandParent record: PK = 1 Rip Field = '1.'
Grandparent record: PK = 2 Parent FK = 1 Rip Field = '1.2.'
Parent record: PK = 3 Parent FK = 2 Rip Field = '1.2.3.'
Child record: PK = 4 Parent FK = 3 Rip Field = '1.2.3.4.'
Grandchild record: PK = 5 Parent FK = 4 Rip Field = '1.2.3.4.5.'

It then becomes a very simple matter to retrieve an entire "hive" of data using a query similar to this:

SELECT * from MyTAble WHERE RipField LIKE < Some Value >

All you need is an insert trigger and an update trigger on the table to maintain the rip field.