query nested records?

[hank scorpio]

Hi--

I've got tough question to ask.

I have two tables (`pieces`,`builds`). The `pieces` table contains all the pieces I use for everything. The `builds` table contains all the items I've built using records from pieces.

For example:

PIECES:
..piece1
..piece2
..piece3
..piece4
..piece5

BUILDS
..build1
..piece1
..piece2

build2
..piece3
..piece4

build3
..build2
..piece5


From this sample, you can see my build1 components consists of piece1 and piece2. My build3 uses the built item called build2, plus piece5.

My question is, when I add a new record to `build`, i want to make sure I'm not adding pieces in a circular reference. For example:

build5
..build4
..piece11
..piece12

build4
..build5

Since build5 contains build4, I want to make sure I can't add build5 to build4. I need to check all the levels deep in a build to make sure this never happens.

Does anyone have any suggestions on this?

Thanks!

--hank

[MAK]

which RDBMS?

[MAK]

If it is sql server 2005, you could use Common Table Expression to get the hierarchial data with recursion.

http://entropia-online.blogspot.com/...mon-table.html