0

I am wondering how can i found all menus and their sub menus .

i have got table "menu" which contains:

  1. id as PK
  2. parent_id as FK to menu.id
  3. title character

and i have got in my database record for example: 

id: 2 parent_id = 1
id: 3 parent_id = 2
id: 4 parent_id = 2
id: 5 parent_id = 3
id: 6 parent_id = 5
id: 7 parent_id = 4
id: 8 parent_id = 7
id: 9 parent_id = 8
id: 10 parent_id = 1
id: 11 parent_id = 10

and i need to create select statement and get all sub menus for menu id = 2 ?? so its something like recur-ency select ... my only 1 parameter is id = 2 and in result i must have got menu with id: 3, 4 ,5 , 6, 7,8 , 9 without 10 and 11 menu.

This statement must working with postgresql and oracle databases

in oracle i try to write something like that : 

 with q(id, parent_id) as (
  select 
    t1.id , t1.parent_id
    from menu t1
    where t1.id = 454

    union all
    select 
    q.id , q.parent_id
      from q
      join menu t2 on q.id = t2.parent_id
)
select * from q

but i have got loop error ORA-32044 i don`t know oracle database version so this statement must be correct with all oracle database version >= ver 9

Łukasz Woźniczka
  • 1,625
  • 3
  • 28
  • 51
  • 3
    You'll be lucky to get something that works in both DBs. PostgreSQL supports the SQL-standard `WITH RECURSIVE` common table expression, wheras Oracle supports only `CONNECT BY` until Oracle 11g r2. See http://stackoverflow.com/questions/4659803/recursion-in-oracle . Honestly, your app should be prepared to issue different queries to different databases in some cases. – Craig Ringer Mar 04 '13 at 13:40
  • i hate oracle in postgresql every sql statement is easy to write and intuitive but of course not in oracle ... – Łukasz Woźniczka Mar 04 '13 at 14:51
  • 1
    Oracle's recursive `with` does have some nice extensions which I would love to see in Postgres as well. And the `CONNECT BY` syntax is *much* easier to write than a recursive CTE. –  Mar 04 '13 at 15:03
  • @a_horse_with_no_name Agreed; while in general I tend to find Oracle's SQL syntax absolutely awful when I've had to deal with it, it seems that they did a good job with `CONNECT BY`, wheras recursive CTEs are powerful but certainly not intuitive. – Craig Ringer Mar 04 '13 at 23:49

0 Answers0