show the parent of a data using connect by prior?

Question

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

score 0 · Accepted Answer · answered Sep 12 '16 at 17:28

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

show the parent of a data using connect by prior?

1 Answers1