Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
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. –  a_horse_with_no_name Jan 11 '13 at 17:01

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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