BoM type table Join

[John Flanagan]

I have two tables which combined complete a 'Bill of Materials' Type table join. i.e. table 1 links to table 2 and table 2 links back to table 1.

As an example of this I have included the following query

select p2.orderno, p1.pegging_type
from mrp_plan p1, mrp_plan p2, mrp_pegging g
where p1.plan_sequence = g.plan_sequence and
g.peggingno = p2.peggingno and
p2.pegging_type = 'P' and
p2.orderno like 'W000%';

This query climbs one level up the pegging tree to tell me if the demand is from a sales order or a forcast.

My question is what is the proper structure for this type of join and how do I climb up multiple levels?

DB software is Oracle 8.1.7

[Jean-Luc Oudart]

You probably have to use the "walking tree" technic if I understand your Query.

I assume you have Metalink access.
search for "walking tree"

regards,
Jean-Luc

[Michael Schulte zur Sur]

Hi,

what do mean by climbing up levels?
I was looking for a way to combine three tables in metalink and this I found:http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=333481.999

try this:
select p2.orderno, p1.pegging_type from (mrp_plan p1 left join mrp_pegging g on p1.plan_sequence = g.plan_sequence) left join mrp_plan p2 on g.peggingno = p2.peggingno where p2.pegging_type = 'P' and p2.orderno like 'W000%';

greetings,

Michael

[John Flanagan]

I have looked at the Wakling tree situation but it seems only to work for a single table.

In my case I have 2 tables.

mrp_plan links to mrp_pegging by peggingno. I can then use plan_sequence field to link mrp_pegging back to my parent record in mrp_plan.

Is there a good way to do this?
Is my explanation make sense?

Regards,

John.