Forum OpenACS Development: query that shows connection betwen objects...

I have an objects table that connects different objects with each other (columns: object_id_1, object_id_2), while it could be possible that an object_id_1 is related to an object_id_2, but the above object_id_2 is not related to the above object_id_1. In this case only one entry would exist. Whenever  two objects are related to each other and vice versa two entries would exist.

I am looking for a query that returns a list of object_id_2 that is related to one specific object_id_1, which is not that hard in itself:

select object_id_2 from objects where object_id_1 in (select object_id_2 from objects where object_id_1 in (select object_id_2 from objects where object_id_1 = specific_object_id_1)) and so on.

But I would additionally like to know how the objects are related to each other. For example:

specific_object_id_1, object_id_2, object_id_2, object_id_2

Any suggestions?

Hey Tom, I am looking for a friendster style query that shows how one user (object_id_1) is related to another user (object_id_2) via my objects table (ala 6 degrees of separation)...

An example:

object_id_1  object_id_2
1            2
2            3
3            4
4            5

1 is related to 5 via 2,3,4...

I really have no idea, not having thought of this before.

But my first instinct would be to use hierarchical queries of some sort. Would they work?

I'd also look into some SQL reference books, like SQL for Smarties, or something.

Maybe modeling graphs in SQL?