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
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
``````

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
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
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