|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Complex Query (atleast I think so)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?
-- Show quoteArnie 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.
Show quote "Arnie Rowland" <ar***@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 -- Show quoteArnie 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" <ar***@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. >>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics