Drill down through pivot table with one query ?
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:Me Too
03/11/10 11:34 P
02/23/11 04:54 P
Author: Me Too
03/11/2010 11:34 PM
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.
Author: Nathanael Brubaker
02/23/2011 04:54 PM
Hi - Did you figure this out? Any tips on creating it? Thanks!
May 18, 2013
Latest Fusion Authority Articles