return parent id from a tree like structure

Question

I'm trying to get all children ids (multi levels) which are related to a parent. I thought using Recursive would do the trick, having attempted the query it's not returning the expected result and I'm not even sure this is the write way to go about this or where I'm going wrong.

Expected result is to return all cities and counties for a specified location.

For example if I pass the id for England (id=1) I'd like to return all the ids associated to it. It can be up to 4-5 level of association

For example

Id      Location
-----  ---------------
1      England
3      London            (as its associated with England)         
5      Ealing            (as associated with London)
6      Westminster       (as associated with London)
7      Camden            (as associated with London)
8      Barnet            (as associated with London)

. . .

SQL Fiddle example

Can someone please point me in the right direction on how to resolve this. Is this the correct way to approach this problem?

thanks.

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

This tutorial gives a good overview of storing hierarchica data in sql.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Originally from this question: How do I represent a Tree Structure in a mySQL table

edited May 23 '17 at 12:05

Community

1
1

answered Oct 22 '14 at 12:10

Owen

209
1
13

score 0 · Accepted Answer · edited Oct 23 '14 at 10:40

You are moving in the right direction.

Consider the result I've achieved:

;WITH locationFamily AS
(
    SELECT   id
            ,location_name 
            ,parent_id
            ,0 as level
    FROM location
    WHERE parent_id IS NULL
    and id = 1
    UNION ALL 
    SELECT   ls.id
            ,ls.location_name 
            ,ls.parent_id
            ,level + 1
    FROM location   ls
    INNER JOIN locationFamily  lf ON ls.parent_id= lf.id
)
SELECT *
FROM locationFamily

move the id condition to the first select in the CTE, change aliases for id's connections.

return parent id from a tree like structure

2 Answers2