5

I have inherited a table that contains a column that is used to "chain" between various related rows within the same table.

For instance: id, bar, reference_id

  • 1, 'foo', 2
  • 2, 'foo', 3
  • 3, 'foo'

I have no idea what to call this structure in order to search for how I should deal with it. The idea is that the table maintains a sort of living history within itself. (my problem is that I may have row 1 or 2, but I need to get to row 3 somehow that is agnostic to the number of 'levels' that I need to traverse)

1

2 Answers 2

7

There are several things in play here. A table that has a self-referential key is said to have a reflexive foreign key, though that doesn't look like it applies here because you'll have missing values.

What you're looking for is a hierarchical query, which can be achieved in Oracle by using a CONNECT BY clause.

They can be a bit difficult to get your head around at first, but there are plenty of good examples on the internet. A good one to start with is here. For once, the official documentation is quite clear too!

Something like:

SELECT id, bar, reference_id, LEVEL
FROM yourtable
CONNECT BY PRIOR reference_id = id;

... will get you started on your data.

1
  • It can very well be a foreign key, even if the column is null. It then simply points to nowhere. Any non-null value must "point" to an existing primary key, but it is perfectly valid to have a null value in such a column.
    – user1822
    Jan 11, 2013 at 17:01
5

A term you'll see used for this type of table is hierarchical data/table/queries – the table represents a hierarchy, or parent/child relationship of some form. (Sometimes they're simply referred to as trees.)

Oracle has specific SQL extensions for hierarchical queries, namely the start with/connect by keywords.

If you're only looking for leaf rows (i.e. rows with no next "pointer"), you can use the CONNECT_BY_ISLEAF in a where clause. Something like:

select
  name
from
  foo
where
  CONNECT_BY_ISLEAF = 1
connect by
  prior nxt = id
start with
  id = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.