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 SonIn 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 firstname.lastname@example.org 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.