Hello everyone,

What I'm gonna ask is kinda complicated so I'll try to be as clear as possible.

I'm trying to make large system that works with "connections", so you can have items connected to each other, for example, you can have a bank of sections, and connect them as you wish to make sub-sections et so on.

So you link your sections, you can link them to one or more menus, and connect them to one or many projects. Basically, it let's you configure your data in any pratically any way you wish.

So everything is alright with the system itself, but I've come to a point where a lot of data must be recovered with many items connected with each other.

For example, if I connect a section to another section, I need to get all connections back to the menu to make sure that the menu accepts sub-sections or sub-sub-sections or any lower level section.

The challenge, is that, since anything can be connected basically anywhere, I don't know how many "levels" there are, or in other words, I don't know how much "parent items" my "child item" as, so I cannot know how to formulate my query since I don't know how many items I must get...

I don't know if I'm clear enough because a lot of details are needed to understand.

But basically, is there a way to loop or add conditions inside a query? Would there be another way then making one query to check each level since that could use a lot of queries for one verification.

Any suggestions/comments would be appreciated and don't hesitate to ask questions if you need more details...

Chris

do a search on tree traversals, specifically I'd recommend searching on Joe Celko

This thread explained a lot about different ways of storing hierarchies. Can you have loops in your connections or do they spread out like a tree (or hierarchy)?

You can calculate all the children under a connection by first inserting the original connection(s) into a temp table. Then repeat inserting any children of the connections in your temp table that haven't already been inserted. Keep looping until you don't add any more children. Stop and return the rows in your temp table. Do all this in a stored proc with a repeat loop.

If the connections don't change too often then I'd use a cache table to hold all the children underneath any given connection - this will make getting all the children much much faster but you will need to recalculate the children on a regular basis.

Mike