robin g robin g - 2 months ago 6
SQL Question

show the parent of a data using connect by prior?

i have this this table where i have

parentitem | childitem ---table name
dinner | steak
dinner | wine
dinner | mashed potato
dinner | coffee
coffee | sugar
coffee | water
dinner | cake
cake | liquid syrup
cake | egg


i want to retrieve all the childitem of 'dinner' using connect by prior i used the ff code

Select Level, LPAD('->',2*(LEVEL-1))||CHILDITEM From table
Start With parentitem = 'dinner' Connect By Prior childitem =
parentitem


but it doesnt include the parent item which is 'dinner' but it produces all the child items of dinner correctly, btw my friend hinted me to use union. I am using oracle .

so my expected result is

LEVEL | CHILDITEM
0 | dinner
1 | steak
1 | wine
1 | mashed potato
1 | coffee
2 | sugar
2 | water
1 | cake
2 | liquid syrup
2 | egg

Answer

The problem is that you do not have "dinner" as a child item in your data.

If you did, it would be a simple matter of START WITH childitem='dinner'.

E.g.,

SELECT LEVEL,
       LPAD ('->', 2 * (LEVEL - 1)) || childitem
FROM   t
START WITH childitem = 'dinner'
CONNECT BY PRIOR childitem = parentitem

As things are with your data, if you want "dinner" to be listed as a child item, you'll need your query to create a row that doesn't exist. UNION ALL is as good a way as any for that. E.g.,

SELECT 0 AS "LEVEL",
       'dinner' childitem
FROM   DUAL
UNION ALL
SELECT LEVEL,
       LPAD ('->', 2 * (LEVEL - 1)) || childitem
FROM   t
START WITH parentitem = 'dinner'
CONNECT BY PRIOR childitem = parentitem

Another way would be to use UNION ALL to create the row that is missing in your source data. E.g.,

SELECT LEVEL,
       LPAD ('->', 2 * (LEVEL - 1)) || childitem
FROM   (SELECT NULL parentitem,
               'dinner' childitem
        FROM   DUAL
        UNION ALL
        SELECT parentitem,
               childitem
        FROM   t) t
START WITH childitem = 'dinner'
CONNECT BY PRIOR childitem = parentitem
Comments