Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Interfaces/SQL    [ Add a report in this area ]  
Report #:  1447   Status: Reported
Hierarchical Queries
Project:  InterBase Build #:  6.0.2
Version:    6.0 Submitted By:   Mathias Burbach
Report Type:  Suggestion / Enhancement Request Date Reported:  6/5/2002 4:02:23 PM
Severity:    Infrequently encountered problem Last Updated: 5/2/2003 12:58:35 PM
Platform:    Not OS or platform specific Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: (1 Total Rating)
2.00 out of 5
Total Votes: None
Description
If you start storing hierarchies in a table by using a so-called "fishhook" you will need to retrieve the records in a hierarchical structure at some point later. Oracle knows the START WITH and CONNECT BY clause for a SELECT command. Something similar in InterBase would be helpful.
Steps to Reproduce:
Create Table Person
  (PersonID Integer Not Null,
   Name VarChar(50),
   MotherId Integer,
   FatherId Integer,
   Constraint XPKPerson
     Primary Key (PersonID));

Alter Table Person
  Add Constraint FK_Person_Mother
    Foreign Key (MotherID)
    References Person;

Alter Table Person
  Add Constraint FK_Person_Father
    Foreign Key (FatherID)
    References Person;

Insert Into Person (PersonID, Name) Values (1, 'Peter Smith');
Insert Into Person (PersonID, Name) Values (2, 'Albert Smith');
Insert Into Person (PersonID, Name) Values (3, 'Alison McArthur');
Insert Into Person (PersonID, Name) Values (4, 'Edward Smith');
Insert Into Person (PersonID, Name) Values (5, 'Beatrice Wonder');

Update Person Set FatherID=2, MotherID=3 Where PersonID=1;
Update Person Set FatherID=4, MotherID=5 Where PersonID=2;

Commit;

And now the SELECT command as it would work in Oracle:

Select Name
From Person
Start With PersonID = 1
Connect By Prior MotherID = PersonID;
Workarounds
None
Attachment
None
Comments

John Small at 8/1/2002 3:16:30 AM -
The best method I've found to deal with this is based on the fact that SQL is a set based language and hence hierarchies can be represented as nested sets.  Joe Celko gives a good description of this in his book "SQL for Smarties", he's also got a web page on the subject, search Google for "Joe Celko" and "nested sets".

Queries are much much faster than when traversing an adjacency list structure like the one described here, but the price is more coding upfront when putting things into the sets in the first place.

Server Response from: ETNACODE01