compare elements of one table multiple times

[aarellano]

Hello,

I have a table called pr, this table consists of two fiels, parent and child.
the parent can have a child and the child can be a parent as well, this can happen multiple times
so I was trying to put the same table multiple times and joining the parent to the child of the next table.
for example
table1 table2 table3
parent Child parent child parent child
000 a ab 000 ac ab
011 a1 ab1 011 ac1 ab1
012 a2 ab2 012 ac2 ab2
013 a3 ab3 013 ac3 ab3

The problem with this is that I don't know up to how many tables I would have to put to get all the different levels

is there an easier way?

Thanks!!!!

 

[lespaul]

so one table with parents and children:

[tt]
parent Child
000 a
011 a1
012 a2
013 a3
ab 000
ab1 011
ab2 012
ab3 013
ac ab
ac1 ab1
ac2 ab2
ac3 ab3[/tt]

so all your records listed above are in a single table with parents and children related....why would you think you needed more than one table?

Leslie

Come join me at New Mexico Linux Fest!

 

[aarellano]

yes they are in a single table parent always has a child, the child can then become a parent and so on.
not sure how to go about this one

 

[aarellano]

this is what my table looks like

parent Child
000 a
011 a1
012 a2
013 a3
ab 000
ab1 011
ab2 012
ab3 013
ac ab
ac1 ab1
ac2 ab2
ac3 ab3



This is what I would like it to look like

parent Child parent child parent child
000 a ab 000 ac ab
011 a1 ab1 011 ac1 ab1
012 a2 ab2 012 ac2 ab2
013 a3 ab3 013 ac3 ab3

or this

Parent child parent parent
000 a ab ac
011 a1 ab1 ac1
012 a2 ab2 ac2 , etc.

I was taling to a collegue and he was suggesting that it would be kind of like exploding a bill of material, I am not too familiar with that though

 

[lespaul]

again why would you want to do that? your current solution of a single table is the correct way...

Leslie

Come join me at New Mexico Linux Fest!

 

[aarellano]

because I need to calculate the different items that go into one bag, one bag can go into another bag, so one bag can have multiple bags with those bags having different things

 

[lespaul]

Ok so let's say you have a widget in your inventory...you make widgets by using other items in your inventory. So you create a bill of materials for a widget....so we'll say that a widget is made up of:

4 1x1 metal plates
10 1" screws
5" foam

So in our inventory table we will have:
[tt]
ID Description
1000 1x1 metal plate
2000 1" screw
3000 Foam
4000 Widget
[/tt]

and then we will have a BOM table that shows us what makes up certain items:
[tt]
(Parent) (Child)
ID InventoryID PartID Qty
1 4000 1000 4
2 4000 2000 10
3 4000 3000 5[/tt]

you have a table that shows your parents and children (the inventory table) and a related table (the BOM table) that shows what children belong to which parent.

HTH

Leslie