Recursive-ish query for tags?

I have a table of tags that can be linked to other tags and I want to "recursively" select the tags in order of arrangement. So that when a search is made, we get the immediate (1-level) results and then carry on down to say 5-levels so that we always have a list of tags no matter if there wasn't enough exact matches on level 1.

I can manage this fine with making multiple queries until I get enough results, but surely there is a better, optimized, way via a one-trip query?

Any tips will be appreciated. Thanks!

Results:

tagId, tagWord, child, child tagId
'513', 'Slap', 'Hog Slapper', '1518'
'513', 'Slap', 'Corporal Punishment', '147'
'513', 'Slap', 'Impact Play', '1394'

Query:

SELECT t.tagId, t.tagWord as tag, tt.tagWord as child, tt.tagId as childId

FROM platform.tagWords t

INNER JOIN platform.tagsLinks l ON l.parentId = t.tagId
INNER JOIN platform.tagWords tt ON tt.tagId = l.tagId

WHERE t.tagWord = 'slap'

Table Layouts:

mysql> explain tagWords;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| tagId   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tagWord | varchar(45)         | YES  | UNI | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

开发者_StackOverflowmysql> explain tagsLinks;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| tagId    | bigint(20) unsigned | NO   |     | NULL    |       |
| parentId | bigint(20)          | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

---

AFAIK Mysql doesn't have any mechanism for querying data recursively

Oracle has Connected By construct and Sql Server has CTE(Common Table Expressions).

But Mysql, Read Here and Here

---

Here are the options that I consider each time I find myself in a situation when I need to query hierarchical data.

1. Nested Sets
2. Path enumeration
3. Explicit joins (when the maximum level is known)
4. Vendor Extensions (SQL Server CTE, Oracle Connect by etc)
5. Stored Procedures
6. Suck it up