Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Can recursion handle this?
Message
 
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01221573
Message ID:
01221589
Views:
19
It can be done in SQL 2005 with recursive CTE (Common Table Expressions).

BTW, this line seems to be incorrect because component is referenciong itself
2      2          1       True
>Three tables:
>
>Components:
>Id     Name
>1      Car
>2      Wheel Assembly
>3      Grey Paint
>4      Grey Car
>
>Component List:
>Id     Uses     Quan    ItemIsPart
>1      3          1       True
>1      2          4       False
>2      2          1       True
>2      1          1       True
>2      4          4       True
>3      5          .9      True
>3      6          .3      True
>4      3          1.2     False
>1      4          1023    True
>4      1          1       False
>
>Parts:
>Id     Name
>1      Tyre
>2      Wheel Hub
>3      Engine
>4      Bolt
>5      White Paint
>6      Black Paint
>
>
>So for a Grey Car the required output would be:
>
>PartsList:
>Id     Name          Quan
>1      Tyre           4
>2      Wheel Hub      4
>3      Engine         1
>4      Bolt        1039
>5      White Paint    1.08
>6      Black Paint    0.36
>
>The above is a very simplistic example - in practice we usually need to recurse through 6-7 components to resolve the list. Any suggestions as to the best way to handle this with SQL Server/ADO.NET (I'll refrain from mentioning how simple it was in VFP <g>)
>TIA,
>Viv
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform