Hi Sergey,

>It can be done in SQL 2005 with recursive CTE (Common Table Expressions).

I figured that if it was possible CTE would be needed. Just can't quite get my head around the fact that some rows in the Components table are 'recursive' whilst others reference the Parts table. I'll give it a try (and probably fail :-{....)

Maybe the design is wrong and I need 'dummy' entries for parts in the Components table?

>BTW, this line seems to be incorrect because component is referenciong itself
>

>2      2          1       True
>

Hmm. I did the example 'by hand' so there could be mistakes but I think that is OK - i.e Wheel Assembly uses one Wheel Hub (ItemIsPart)

Regards,
Viv

UPDATE: **** I actually got it to work !!!! ***********

>
>>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