storing tags and levels in two tables, need to get tag for levels returned as columns

Hi there I have big problem with one select query

There is a table:

tag_hierarhy
--------------------
|id    |parent_id  |
--------------------
|1     |0          |
|2     |1          |
|3     |1          |
|3     |2          |
|4     |1          |
|4     |2          |
|4     |3          |
|5     |1          |
|5     |2          |
|5     |3          |
--------------------
Comment:
id        - this is the column with tag id
parent_id - this is the id of the tag that is parent of tag with id from column id
One tag can belong to many parents for example: tag 5 belongs to tag 1 and 2 and 3

I have another Table with where I keep levels of tags.

tag_level
--------------------
|id      |level    |
--------------------
|1       |0        |
|2       |1        |
|3       |2        |
|4       |3        |
|5       |3        |
--------------------
Comment:
id    - this is the tag id
level - this is the tag level in hierarhy
0 - continent level,
1 - country level,
2 - region in country level,
3 - city or village in level 2

Now I have to get in one query hierarhy for one tag in one row. For example for tag with id=5 i want to get one row with following data:

-------------------------------------------
| tid | l0_tid | l1_tid | l2_tid | l3_tid |
-------------------------------------------
| 5   | 1      | 2      | 3      | NULL   |
-------------------------------------------
Comment:
tid - the id of requested tag
l0_tid - the id of the tag that tag with id=5 belongs to. This tag must be on level=0
l1_tid - the id of the tag that tag with id=5 belongs to. This tag must be on level=1
l2_tid - the id of the tag that tag with id=5 belongs to. This tag must be on level=2
l3_tid - the id of the tag that tag with id=5 belongs to. Thie tag must be on level=3
Imagine for Example tag 5 is pointing to 'Berlin' so:
l0_tid must point to tag 'Europe',
l1_tid must point to tag 'Germany'... and so on

I can't change this structure because of many performance reasons. Could anybody help me with query for mysql that will be able to fetch data in the way that i described? I'm fighting with this for all day and my mind go blank. I didn't want subquery only joins. I'm looking for some help. Thanks.

Structure of the tables:

CREATE TABLE  `street_traveler`.`tag_hierarhy` (
  `id` int(11) unsigned NOT NULL,
  `parent_id` int(11) unsigned NOT NULL,
  UNIQUE KEY `id_parent_id` (`id`,`parent_id`)
) ENGINE=MyISAM


CREATE TABLE  `street_traveler`.`tag_level` (
  `id` int(11) unsigned NOT NULL,
  `level` int(11) unsigned NOT NULL,
  UNIQUE开发者_JS百科 KEY `id_level` (`id`,`level`)
) ENGINE=MyISAM

Try something like this:

SELECT t.id                       AS tid, 
       GROUP_CONCAT(d0.parent_id) AS l0_tid, 
       GROUP_CONCAT(d1.parent_id) AS l1_tid, 
       GROUP_CONCAT(d2.parent_id) AS l2_tid, 
       GROUP_CONCAT(d3.parent_id) AS l3_tid 
FROM   tag_level t 
       LEFT JOIN (SELECT th1.id, 
                         th1.parent_id 
                  FROM   tag_hierarhy th1 
                         JOIN tag_level t1 
                           ON t1.id = th1.parent_id 
                  WHERE  level = 0 
                  GROUP  BY th1.id) d0 
         ON d0.id = t.id 
       LEFT JOIN (SELECT th2.id, 
                         th2.parent_id 
                  FROM   tag_hierarhy th2 
                         JOIN tag_level t2 
                           ON t2.id = th2.parent_id 
                  WHERE  level = 1 
                  GROUP  BY th2.id) d1 
         ON d1.id = t.id 
       LEFT JOIN (SELECT th3.id, 
                         th3.parent_id 
                  FROM   tag_hierarhy th3 
                         JOIN tag_level t3 
                           ON t3.id = th3.parent_id 
                  WHERE  level = 2 
                  GROUP  BY th3.id) d2 
         ON d2.id = t.id 
       LEFT JOIN (SELECT th4.id, 
                         th4.parent_id 
                  FROM   tag_hierarhy th4 
                         JOIN tag_level t4 
                           ON t4.id = th4.parent_id 
                  WHERE  level = 3 
                  GROUP  BY th4.id) d3 
         ON d3.id = t.id 
GROUP  BY t.id 

This returns:

+---+------+------+------+------+
| 1 | NULL | NULL | NULL | NULL |
| 2 | 1    | NULL | NULL | NULL |
| 3 | 1    | 2    | NULL | NULL |
| 4 | 1    | 2    | 3    | NULL |
| 5 | 1    | 2    | 3    | NULL |
+---+------+------+------+------+

You need to have indexes on

• (tag_level.id,tag_level.level)
• (tag_hierarchy.id,tag_hierarchy.parent_id)