Select top most parent from self foreign key related SQL table

Question

I have following SQL table

Area

id     name         parent_id
1      Pune         null
2      Mumbai       null
3      area1        1
4      area2        3

Here I have logical foreign key parent_id so that area2 has the parent area1 and area1 has parent Pune. Here I want to select topmost parent of any subarea. For ex. when i search area2 then it will give result Pune.

What query you tried and what error you got? – Gunaseelan May 23 '15 at 07:01 — Gunaseelan, May 23 '15 at 07:01

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

mysql does not support hierarchical joins (e.g. WITH RECURSIVE)

You can either try this solution:

Hierarchical queries in MySQL

Or manipulate the recursion within php or any other server-side language you are using

Since you asked in the comments regarding Oracle / MSSQL: Oracle,MSSQL

edited May 23 '17 at 11:54

Community

1
1

answered May 23 '15 at 06:52

Uri Goren

13,386
6
58
110

I've added links for mssql & oracle, – Uri Goren May 23 '15 at 09:07
If this answer was helpful please accept it as answer or at least vote up, thank you. – Uri Goren May 23 '15 at 09:07
How should I do this using postgresql? – Tushar Kulkarni May 08 '17 at 15:42

Select top most parent from self foreign key related SQL table

1 Answers1