sorting en calculating on internal relation

[martijn]

We have a table with cargo details
it is build up like this

CargoID | CargoByID | CargoAmount | CargoPackaging | CargoWeight

the CargoByID contains the ID of a the upper detail

An example of the database would be

1 | 1 | 2 | pallet | 10
2 | 1 | 10 | boxes | 0,1
3 | 2 | 200 | boy toys | 0,1
4 | 1 | 5 | boxes | 0,1
5 | 4 | 250 | girl toys| 0,2

i have to make a view which gives a correct list of the packing like
1 pallet 10 boxes 200 boy toys
5 boxes 250 girl toys
at this moment I just have build a string with the id's in it separated by "-" to sort the table an calculate from there.

for record 1 the string would be just 1
for record 3 the string would be 1-2-3

then I can use a for loop

But there must be a smarter way

[ROCLASI]

Hi Martijn,

Essentially you are talking about hierarchical data, i.e. trees.

Take a look at my presentation from ServoyWorld 2008 on this subject. It was inspired by this blog post from PostgreSQL community member Hubert Lubaczewski (a.k.a. Depesz).
It explains how to use an adjacency list to query your tree with 1 single query (and also sort).

Hope this helps.

[martijn]

Hi ROCLASI,

certainly seems to represent my problem. Thanks for the link