formating SQL query "start with...connect by"

I can select relatives in hierarchical order by using:

select first_names, last_names
from family_relations
start with father is null
connect by prior relative_id = father;

But I lose that structure by listing names in a "flat" way using html ul and li tags. Is there any way to represent the hierarchy through clever querying/indentation?

For example, can we select out only those persons whose ancestors have 0 or 1 or 2 ancestors? How could we then use this information to write out each level without making another query to the database?

-- David Sirkin, October 9, 1999

Answers

Yes! Oracle provides a variable called level, which in connect by queries tells you how deep you've gone. To get the indentation right, you multiply the level by some number of spaces ( ). Check out /intranet/org-chart.tcl

-- Andrew Grumet, October 9, 1999

I wrote a new trees chapter of http://photo.net/sql/ to explain this stuff. I also fixed the typo in ps5.adp so that the last name column is "last_name".

-- Philip Greenspun, October 9, 1999