Problem solve Get help with specific problems with your technologies, process and projects.

Hierarchy support in Oracle and SQL Server

I want to write a query for inheritance. The data in my table (just dummy data for example) is as follows:

ID      Parent ID         Description
--      ---------         -----------
1      -1                 Grand Grand Father
2       1                 Grand Father
3       2                 Father
4       3                 Son
5       4                 Grand Son
In the above table, suppose I receive an ID of 5 from the user and I have to show him all the parent rows for ID 5 along with the row of ID 5. How can I show that?

Oracle has the feature Connect By Prior .... Start With. I don't know how to get this in SQL Server 7.0. Please, I will be greatful if you can give a solution for the above query.

There is a very good reason Oracle implemented the CONNECT BY feature. That reason is that without it, doing what you want can be a royal pain. Unfortunately, SQL Server is lacking in its support for hierarchies. I see two ways to get around this. One option is to change how you store your hierarchy, such that each inherited relationship is stored. Take a look:

ID ParentID Level
-- -------- -----
 5        4     1
 5        3     2
 5        2     3
 5        1     4
 4        3     1
 4        2     2
 4        1     3
 3        2     1
 3        1     2
 2        1     1

The other option is to use some sort of WHILE loop in Transact-SQL to walk down the hierarchy and populate a temporary table. Ken Henderson's book, "The Guru's Guide to Transact-SQL," covers this in his chapter on hierarchies.

Neither one of these options is necessarily pretty, but both will work.

For More Information

  • What do you think about this answer? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle, SQL Server, or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle, SQL Server, and SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.