Join query issue in MySQL

Question

I'm storing the records in hierarchy. Ex.

Account -> Hospital -> Department
Account -> Hospital -> Department -> Section

I'm storing the association of all the 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 with id 1, follows first hierarchy; whereas account with id 2 follows second hierarchy.

I need to fetch the records for the given level. Ex.

1. Get all the devices belonging to account with id = 1
2. Get all the devices belonging to department with id = 200 and account with id = 2 and so on.

I can retrieve these with queries like:

First query:

SELECT a3.ChildType, a3.ChildId FROM 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 AND a3.ParentType = a2.ChildType -- [got device level]
WHERE a1.ParentId = 1 AND a1.ParentType = 'account' 
 AND a3.ChildType = 'device'

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

But now, if I want to associate device against hospital level instead of department level; like:

|    xx|    hospital|        10|         device|       1003|

then for the same query this device will be skipped and only the devices associated with department level will be returned. How can I get all the devices (i.e. under both hospital level and department level).

Answer 1

That is a horrible way to store data.

I suggest restructuring and creating separate tables each entity.

I.e. create table account, create table hospital ...

Then you can jion properly. Everything else would require dynamic iterative selection which is not built in to mysql and needs to be done with an external program or by hand.

You can write a script to dynamicall generate a table for each parenttype and childtype though.