1

If there is a table which as 2 columns, ParentId and ChildId and there could be multiple levels, as table below

ParentId    ChildId
   -1             1
   -1             2
    1             3
    3             4
    4             5

If the child has no parent it is indicated by -1.

Now if I need the parent at the topmost level for say 5 , then its parent is 4 whos parent is 3 whos parent is 1. So 1 is the answer as its parent = -1.

I went through the discussion in Sql server CTE and recursion example, seemed complex.

From googling I got to know that we can use WITH AS and UNION to achieve this, can any one shed some light on a generic sql that can get this result ?

thanks

Nohsib

Community
  • 1
  • 1
Nohsib
  • 3,614
  • 14
  • 51
  • 63
  • What exactly is the output that you want the SQL statement to return? Do you want the entire hierarchy? Or just the highest-level parent? If you want the hierarchy, in what format do you want it? Multiple rows? A concatenated formatted string? Something else? – Justin Cave Mar 20 '13 at 22:33
  • @ Justin : Just the id of the highest-level parent – Nohsib Mar 20 '13 at 23:37

1 Answers1

4
select connect_by_root(ChildId) as id
from t
where ChildId = 5
start with ParentId = -1
connect by ParentId = prior ChildId

fiddle

Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • Thankyou Egor. This works fine from the sql developer but through the application that uses the below code it shoots out the exception with SQLCODE=-313, SQLSTATE=07004 – Nohsib Mar 21 '13 at 03:46
  • Properties props = new Properties(); props.setProperty("user", username); props.setProperty("password", password); Connection conn = null; conn = DriverManager.getConnection(url, props); Statement statement = conn.createStatement(); statement .executeQuery( "select connect_by_root(ChildId) from t where ChildId = 5 start with ParentId = -1 connect by ParentId = prior ChildId"); – Nohsib Mar 21 '13 at 03:49
  • @Nohsib - Try adding alias for column (see my updated answer). – Egor Skriptunoff Mar 21 '13 at 13:34
  • @ Egor : Still no luck :( same exception – Nohsib Mar 21 '13 at 15:19
  • @Nohsib - 1) Could you successfully execute `select dummy from dual`? 2) Is `t` real name of your table? 3) Try to prefix schema name to table name: `schema.t` – Egor Skriptunoff Mar 21 '13 at 15:28
  • @Nohsib - 4) Is your database really Oracle DBMS, not DB2 ? – Egor Skriptunoff Mar 21 '13 at 15:29
  • @ Egor : Issue was with my database the query works 'as is' in your solution. It works both on oracle and db2. Accepting it. Thankyou. – Nohsib Mar 21 '13 at 15:52