Bill-of-Material processing 1

[rmuller]

I searched the Internet and looked into my SQL books, but could not find the answer, so i hope someone here can help me!
I have a table like this (simplified):

CREATE TABLE Products (
    ProductId COUNTER CONSTRAINT ProductsPK PRIMARY KEY,
    ProductCode CHAR(8) NOT NULL,
    ParentId LONG CONSTRAINT ProductFK1 
        REFERENCES Products (ProductId),
    LevelCode BYTE);

For this table i need a 'exploded view' and an 'imploded view'.

Exploded view: all products that are decendants of the given product.
Imploded view: all ancestors (parents, grandparents and so on, up to and including a product with LevelCode=0).

Any SQL guru who can help me?
Thanks in advance

Ronald

 

[MichaelRed]

Visio (if you have it) has a good example of this type of query, using an organization structure.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[rmuller]

tnx michael (again!)

i do not have visio (i use smartdraw), can you post the query, it will be VERY helpfull for me, so thanks in advance!

 

[tlbroadbent]

Check these links.

http://www.sqlteam.com/ItemPrint.asp?ItemID=1353

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=19663

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/recursion.asp

http://www.dbpd.com/vault/9811/kamfn.shtml

Joe Celko discusses "common questions about SQL trees and hierarchies" on this page.

http://www.intelligententerprise.com/001020/celko.shtml

Celko discusses "nested sets" on this page.

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[rmuller]

thanks terry!

very helpfull, i implemented celko's nested set approach. Works great!