Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WastedCharlie
Starting Member
2 Posts |
 Posted - 2011-07-18 : 14:06:12
|
| Hi Sql Team Forum Peoples. Sorry that my first post here is a question, but I've gotten myself into a design that feels rather out of my depth and its solution is time-critical and holding me up, so I'm desperate for help.I'm working with a self-referencing table for the first time. This structure is a bit of a pain but it appears to be the best for my requirements.I have tables with the following roughly the following structure:Table "Categories":Fields:ID int PK,Name varchar NOT NULL,ParentCategoryID int NOT NULL*,Rank int*This is the self-referencer. Root Categories have Parent ID = Own ID.Table "Items":ID int PK,Name varchar NOT NULLTable "GroupingTypes":ID int PKName varcharTable "Groupings":ID int PK,CategoryID int NOT NULL,ItemID int NOT NULL,GroupingTypeID int NOT NULL,I've used the self-reference because my items need to to sit in categories of 1 - an-indeterminate-number-of-levels deep. Rank determines the order in which a category appears in relation to its siblings (alphabetised won't do).What I need is a procedure that will take a given GroupingType record and determine which Categories are used within it.THe bottom-levellers are easy because they're directly ID'd by Groupings table, but each category can be nested indefinitely (in truth, the nature of the data is that they're never likely to be more than 5 deep, and more likely to be 2 - 3 deep).I'm having terrible trouble determining each category's lineage from the root, and I'm even not sure on the best way to represent the results.It's also imperative that I determine the route to the root since I need to be able to assess whether a category appears in its own path (rather than being a root) and therefore attempts to create a circular reference.I've seen a few procs that determine a string path, e.g.:CategoryID int, Path varcharso that each record looks something like ID = x, Path = "root name/category level 1 name/category level 2 name/etc./category x name" but this feels inappropriate since my application needs individual id's for each level between X and root (inclusive); Plus I've not proven skilful enough to mould these examples to my own ends.I *am* able to get the descendants of a given category. I'm using an adaptation of a method by Alexei Fimine to return a table with the following structure:CategoryID, Category Name, Parent Category Name, Depth from Root, Parent's Rank, Own Rank.Any help would be greatly appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-18 : 15:49:59
|
| your (linked) solution is sort of old school. 2005 and later have introduced Common Table Expressions (CTE). You can set up a Recursive CTE. I'm happy to post an example but they have them in Books Online as well.Avoiding circular references is a little trickier with CTEs then with the old-style school but can be done efficiently (again - happy to post an example).If you want a specific solution to your problem then please post the actual DDL of your table(s), DML to populate your table(s) with some sample data, and Desired output based on the sample data you provide.Be One with the OptimizerTG |
 |
|
|
WastedCharlie
Starting Member
2 Posts |
Posted - 2011-07-19 : 10:52:46
|
| Thanks for the advice TG. I am a bit old school. This is for the resurrection of an old project stored on Sql 2000, but I've recently acquired Sql 2008 and I'm really glad I've got it for this project. Those CTE's look to be exactly what I need. I've read through a dozen or so examples this afternoon and they look very straight-forward and usable. |
|
|
|
|
|
|
|
|
Advice on self-referencing table