how can i 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. im using oracle sql

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

Don't tag products not involved. You can put one of the tags back, the one for the dbms you're using. — jarlh, Sep 09 '16 at 08:38

score 0 · Answer 1 · answered Sep 09 '16 at 09:32

WITH roots ( parentitem ) AS (
  SELECT 'dinner' FROM DUAL
)
SELECT DISTINCT
       0 AS "level",
       parentitem AS childitem
FROM   table_name t
       INNER JOIN roots r
       ON ( t.parentitem = r.parentitem )
UNION ALL
SELECT LEVEL,
       LPAD( ' ', LEVEL * 2, ' ' ) || childitem
FROM   table_name
START WITH parentitem IN ( SELECT parentitem FROM roots )
CONNECT BY PRIOR childitem = parentitem;

how can i show the parent of a data using connect by prior?

1 Answers1