Chaining pattern?

[svivian]

This will probably sound quite complex, but it's not really... bear with me...

 

I have some data which is essentially in a chain format. For example: A->B->C or G->F->E where the letters above would be id numbers pointing to rows in another table.

 

So far, I have a table with the values and a table with the relationships, however, I'm not sure of the best way to get a 'chain' from the database.

 

The 'relationship' table has `from` and `to` fields. My 'view' page will be on one of the elements in the chain and I need to get everything else in the chain, given that element (forwards or backwards). So with this system I'd need several queries to find the next/previous element in the chain and keep repeating.

 

I've thought about adding a `root` field to the table to enable me to select all elements of a chain easily, but then the problem is ordering. I suppose in essence this is a tree pattern, is this easily representable in a database?