I want to fetch database information as per table mentioned below

Question

A table will be having main part and sub part , again main part will be sub part of another main part and it may go till end .

Main Part ------Sub Part
------------------------    
A123 --------------B123
B123 --------------C123
C123 --------------D123
A123 --------------B123
X123 --------------Y123
Y123 --------------Z123

From image above when I put D123 in where condition I need A123 as output .

Query -->

Select * from table where Sub Part = 'D123'

*Output -->*A123

In the same way when I put Z123 in where condition I need to get X123 where.

Query -->

Select * from table where Sub Part = 'X123'

*Output -->*Z123

Expected Query as answer --> I need a query such that it will fetch last main part in the chain

Mike it can be nested 6 times or 4 times or 1 time . it depends on the insertions in Db. its not constant nested. I need a one query such that it will fetch me correct Answer as explained above . — user2936008, Dec 02 '13 at 10:47
I think you would have to first build a list of the codes that are Main Parts SELECT DISTINCT MainPart FROM Table WHERE NOT EXISTS (SELECT DISTINCT SubPart FROM Table) Then you will have to build a sub-query nested either once, four times or six times to get the MainPart — Mike, Dec 02 '13 at 11:00
This approach/posting may also help - http://stackoverflow.com/questions/584904/cte-to-traverse-back-up-a-hierarchy — Mike, Dec 02 '13 at 11:02
There are Lakhs of records in DB and I think the solution you have given doesn't work . And if I have to manually do nested condition accordingly then there no use of query . I would have done it but it would take lot of time . If I give an input of sub part I need output which will give last main part . Please give me a function if there is any in SQL Db2 which will fetch this kind of output — user2936008, Dec 02 '13 at 11:05
Checking the approach / posting you have given . will let u know if this is working . — user2936008, Dec 02 '13 at 11:08
I suppose the concept of heirarchy doesn't fit that well with linear data in one table within RDBSM. Typically, you'd have the data in 2 tables with relationships using key values. Let me know how the approach goes - if it works, I'll add it as an answer - always good for the reputation ranking :-) — Mike, Dec 02 '13 at 11:19
yes we need two tables in the approaching you have given me , but it is difficult when there is only one table . I need answer when only one table exists . :( — user2936008, Dec 02 '13 at 11:22
Not sure about your examples. The link that Mike has given you works for finding the last in the line, since it will give you the whole hierarchy you would need to filter it by the highest level (farthest from the original data). This works as long as it is a 1:1 relation ship between main part and sub part. However, looking at your second example, it would not work since X123 is not listed in any sub part of your sample data. — Peter Schuetze, Dec 02 '13 at 22:06

score 0 · Accepted Answer · answered Dec 02 '13 at 12:42

Your idea of getting the last result in an chain with unknown length is not so easy do. If the deepth is 6, as written before, you could try the following SQL-Statement:

SELECT COALESCE(t6.Main_Part,COALESCE(t5.Main_Part,COALESCE(t4.Main_Part,COALESCE(t3.Main_Part,COALESCE(t2.Main_Part,COALESCE(t1.Main_Part,'NO RESULT')))))) as RESULT
FROM table t1
    LEFT JOIN table t2 ON t1.Main_Part=t2.Sub_Part
    LEFT JOIN table t3 ON t2.Main_Part=t3.Sub_Part
    LEFT JOIN table t4 ON t3.Main_Part=t4.Sub_Part
    LEFT JOIN table t5 ON t4.Main_Part=t5.Sub_Part
    LEFT JOIN table t6 ON t5.Main_Part=t6.Sub_Part
WHERE t1.Sub_Part='D123'

otherwise add joins till you meet the wanted max deepth

AWsomee great thankssss :) . It work as expected , like charm .. Thank you soooooo much . depth 6 is more that enough ...... :))) — user2936008, Dec 04 '13 at 09:01

I want to fetch database information as per table mentioned below

1 Answers1