Jon writes "I was thrilled to find an easy example (http://4guysfromrolla.com/webtech/sqlguru/q120899-1.shtml) of how to use T-SQL to perform recursive searches as I'd been trying to automate a manual process using SQL. Unfortunately, the data I am trying to recurse through has an odd table structure.Let's say I am trying to explode out a listing of every component that makes up a piece of equipment, but the table structure is laid out as so:Equipment Number = the equipment to be explodedComponent Number = the components within the equipmentWhere Used Equipment Number = the equipment to be exploded (same as "Equipment Number")As you can see this is an odd layout; inputting an equipment number would yield the following outputEquipment_Number Component Number Where Used Equip. #---------------- ---------------- ------------------Can of CokeCan of Coke Sugar Solution Can of CokeCan of Coke Caffeine Can of CokeCan of Coke Coloring Can of CokeCan of Coke Aluminum Can Can of Coke
We then want to further explode the components within the Can of Coke as these components are further made up of other components and the output would be:Equipment Number Component Number Where Used Equip. #---------------- ---------------- ------------------Sugar Solution Sugar Solution Water Sugar Solution Sugar Solution Sugar Sugar Solution
As you can see, the table structure does not easily lend itself to a recursive search as the list of exploded items ("Component Number") does not include the original Equipment Number upon which you are searching. Is it possible with this table structure to do a recursive search and explode out all components of an item (and all components of components).""
A Variation on Recursive Searches 
Posted - 2005-03-25 : 07:27:30
