Complex Query (atleast I think so)

We have a couple of Bill of Material tables in our SQL 2000 database and I
need to extract information.
The first table is the master table and the only relevant field is fpartno.
Table two is the child table, and it contains all of the parts that make up
the fpartno in the master table. The only two relevant fields are
fcomponent and fcparent.
In the child table a component (Part No) may be in the table several times
because we may use it in different parent parts. And to complicate things,
the child table usually includes multiple levels of material.
How can I query the table so that it will pass over the table as many times
as needed to get the full multiple level bill of material.
Here would be a simple example.
PartA
PartB
PartC
PartD
PartE
PartF
As you can see the BOM for PartA goes out multiple levels. Any ideas on how
I can query this?
Thanks.Oracle has a CONNECT BY clause that makes this easy. Unfortunitly for
SQL Server you have to go thru this mess:
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Preacher Man> wrote in message
news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
> We have a couple of Bill of Material tables in our SQL 2000 database and I
> need to extract information.
> The first table is the master table and the only relevant field is
> fpartno.
> Table two is the child table, and it contains all of the parts that make
> up the fpartno in the master table. The only two relevant fields are
> fcomponent and fcparent.
> In the child table a component (Part No) may be in the table several times
> because we may use it in different parent parts. And to complicate
> things, the child table usually includes multiple levels of material.
> How can I query the table so that it will pass over the table as many
> times as needed to get the full multiple level bill of material.
> Here would be a simple example.
> PartA
> PartB
> PartC
> PartD
> PartE
> PartF
> As you can see the BOM for PartA goes out multiple levels. Any ideas on
> how I can query this?
> Thanks.
>
>|||SQL 2000.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%2319ACkLvGHA.4472@.TK2MSFTNGP02.phx.gbl...
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Preacher Man> wrote in message
> news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
>> We have a couple of Bill of Material tables in our SQL 2000 database and
>> I need to extract information.
>> The first table is the master table and the only relevant field is
>> fpartno.
>> Table two is the child table, and it contains all of the parts that make
>> up the fpartno in the master table. The only two relevant fields are
>> fcomponent and fcparent.
>> In the child table a component (Part No) may be in the table several
>> times because we may use it in different parent parts. And to complicate
>> things, the child table usually includes multiple levels of material.
>> How can I query the table so that it will pass over the table as many
>> times as needed to get the full multiple level bill of material.
>> Here would be a simple example.
>> PartA
>> PartB
>> PartC
>> PartD
>> PartE
>> PartF
>> As you can see the BOM for PartA goes out multiple levels. Any ideas on
>> how I can query this?
>> Thanks.
>>
>|||Perhaps these articles on recursive queries will get you moving in a
direction that works to solve your problem.
http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Preacher Man> wrote in message
news:eLd2X4LvGHA.4160@.TK2MSFTNGP06.phx.gbl...
> SQL 2000.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%2319ACkLvGHA.4472@.TK2MSFTNGP02.phx.gbl...
>> SQL 2000 or SQL 2005?
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <Preacher Man> wrote in message
>> news:u3iA4ULvGHA.4384@.TK2MSFTNGP04.phx.gbl...
>> We have a couple of Bill of Material tables in our SQL 2000 database and
>> I need to extract information.
>> The first table is the master table and the only relevant field is
>> fpartno.
>> Table two is the child table, and it contains all of the parts that make
>> up the fpartno in the master table. The only two relevant fields are
>> fcomponent and fcparent.
>> In the child table a component (Part No) may be in the table several
>> times because we may use it in different parent parts. And to
>> complicate things, the child table usually includes multiple levels of
>> material.
>> How can I query the table so that it will pass over the table as many
>> times as needed to get the full multiple level bill of material.
>> Here would be a simple example.
>> PartA
>> PartB
>> PartC
>> PartD
>> PartE
>> PartF
>> As you can see the BOM for PartA goes out multiple levels. Any ideas on
>> how I can query this?
>> Thanks.
>>
>>
>