Hey all, have a reasonably straightforward setup with a simple request, however I am no SQL expert by any means and feel like there must be a better way to do this. Here's the setup, simplified:
Part table nPartID nCategoryID (...etc..) Category table nCategoryID bActive CatPivot table (nests itself into oblivion) nCategoryID nParentCatID You can see the chain of events: parts have a category attached, and the pivot table sets up the nesting of categories (which can be very shallow or up to 6 or 7 layers deep). What I want to do is SELECT a partID, and [hopefully] in that same query drill down through the CatPivot table to verify that it can reach the bottom of the CatPivot table (the bottom being nCategoryID=0) by way of active categories (category.bActive=1). I don't want to kill it completely if it runs into a disabled category record, because the pivot table lets the categories potentially branch out multiple ways. I just want to verify that at least one of those routes results in an active tree back to 0. This lets me have a part/category in multiple parent categories, but disable some while still having the part active. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3319 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
