how to show in hierarchy ,,

i have "product" table it contains all products details .

product_id  product_type_id    internal_name
1001        finishedgood          nokia  
1002        rawmaterial            chip   
1003        subassembly           diaplay  
1004        rawmaterial           displaybase

it means to make nokia phone( finishedgood) company needs

chip(rawmaterial) , display(subassembly) . to make display(subassembly) it again needs

displaybase ( rawmaterial)

Now "productassoc" table details

product_id   product_id_to 
  1001          1002        
  1001          1003        
  1003          1004       

my question : i want to retrive all objects needed for nokia manufacturing

this query returns level 1 goods be needed .( i want to display raw materials needed for

subassembly also )

query:

select pa.product_id,pa.product_id_to,p.product_type_id,p.internal_name 
        from product p,
        product_assoc pa 
        where p.product_id=pa.product_id_to and  pa.product_id=1001

o/p

product_id  product_id_to  product_type_id        internal_name
  1001           1002           rawmaterial           chip
  1001           1003           subassembly          display

-----

i want to display rawmaterials nee开发者_StackOverflow社区ded for subassembly also .

means , my query should return all the rawmaterials and subassemblys needed to make

finishedgood. and also rawmaterials needed for subassemblys.

this is only an sample . i had around 100 components for a product.

---

I would recommend adding a column called something like "construction"

Make it at least a varchar(255) or bigger if need be to hold the values you'll need, which will be the full chain from parent to the product itself used in the construction of the thing.

Make sure your product numbers are always the same length (or use LPAD with zeroes or spaces if not, to be sure), and populate this with a script that builds these construction strings from the existing data, joined by some character (':', for instance).

This would give you values that look like, from your data:

product_id  product_type_id    internal_name   construction
1001        finishedgood          nokia        1001
1002        rawmaterial            chip        1001:1002
1003        subassembly           diaplay      1001:1003
1004        rawmaterial           displaybase  1001:1003:1004

This gives you an easy column to sort on. Then just make sure that all further INSERTs to the tables follow this logic.

To select from this, just use a LIKE in the WHERE clause:

  SELECT *
    FROM product
   WHERE construction LIKE '1001%'
ORDER BY construction

The fun part of this is you can, in your retrival script, count the number of colons to determine the "depth" of the product. This makes it easy to format the output and such. For instance, in a mod_perl handler outputting HTML you might say:

while (my $p = $get_parts->fetchrow_hashref) {
    $r->print("<li style='text-indent: @{[scalar @{[($p->{construction} =~ /:/]} * 5]}em'>$p->{product_id}: $p->{internal_name} ($p->{product_type_id})</li>");
}