sql - Join query issue in MySQL -

i'm storing records in hierarchy. ex.

account -> hospital -> department account -> hospital -> department -> section 

i'm storing association of records in following manner.

+------+---------------+----------+---------------+-----------+ |  id  |  parenttype   | parentid |   child type  |  childid  | +------+---------------+----------+---------------+-----------+ |     1|        account|         1|       hospital|         10| |     2|        account|         1|       hospital|         20| |     3|       hospital|        10|     department|        100| |     4|       hospital|        10|     department|        101| |     5|     department|       100|         device|       1000| |     6|     department|       101|         device|       1001| |     6|     department|       101|         device|       1002| |     1|        account|         2|       hospital|         30| |     2|        account|         2|       hospital|         40| |     3|       hospital|        30|     department|        200| |     4|       hospital|        40|     department|        201| |     5|     department|       200|        section|       5000| |     5|     department|       200|        section|       5001| |     6|        section|      5000|         device|       2001| |     6|        section|      5001|         device|       2002| +------+---------------+----------+---------------+-----------+ 

so, account id 1, follows first hierarchy; whereas account id 2 follows second hierarchy.

i need fetch records given level. ex.

1. get devices belonging account id = 1
2. get devices belonging department id = 200 , account id = 2 , on.

i can retrieve these queries like:

first query:

select a3.childtype, a3.childid association_lookup a1 -- [got hosp level]  join association_lookup a2 on a2.parentid = a1.childid -- [got dept level]  join association_lookup a3 on a3.parentid = a2.childid , a3.parenttype = a2.childtype -- [got device level] a1.parentid = 1 , a1.parenttype = 'account'   , a3.childtype = 'device' 

i can make dynamic query self joins equal level difference - 1. i.e. account level = 0, device level = 3; hence 2 joins.

but now, if want associate device against hospital level instead of department level; like:

|    xx|    hospital|        10|         device|       1003| 

then same query device skipped , devices associated department level returned. how can devices (i.e. under both hospital level , department level).

that horrible way store data.

i suggest restructuring , creating separate tables each entity.

i.e. create table account, create table hospital ...

then can jion properly. else require dynamic iterative selection not built in mysql , needs done external program or hand.

you can write script dynamicall generate table each parenttype , childtype though.