una tabla recursivos recursivo recursivas recursiva misma hacer ejemplos cursores consultas consulta como mysql tree

recursivos - Encontrar todos los padres en la tabla mysql con consulta única(consulta recursiva)



select recursivo (1)

Tengo este esquema

Data de muestra

| ID | TITLE | CONTROLLER | METHOD | PARENT_ID | |----|-------------------|------------|-------------------|-----------| | 1 | Dashboard | admin | dashboard | 0 | | 2 | Content | admin | content | 0 | | 3 | Modules | admin | modules | 0 | | 4 | Users | admin | users | 0 | | 5 | Settings | admin | settings | 0 | | 6 | Reports | admin | reports | 0 | | 7 | Help | admin | help | 0 | | 8 | Pages | content | pages | 2 | | 9 | Media | content | media | 2 | | 10 | Articles | content | articles | 2 | | 11 | Menues | content | menues | 2 | | 12 | Templates | content | templates | 2 | | 13 | Themes | content | themes | 2 | | 14 | Blog | content | blog | 2 | | 15 | Forum | content | forum | 2 | | 16 | Core Modules | modules | core_module | 3 | | 17 | User Modules | modules | user_module | 3 | | 18 | All Users | users | all_users | 4 | | 19 | Groups | users | groups | 4 | | 20 | Permissions | users | permissions | 4 | | 21 | Import and Export | users | import_export | 4 | | 22 | Send Email | users | send_mail | 4 | | 23 | Login Records | users | login_records | 4 | | 24 | General Settings | settings | general_settings | 5 | | 25 | Email Settings | settings | email_settings | 5 | | 26 | Popular Content | reports | popular_content | 6 | | 27 | Most Active Users | reports | most_active_users | 6 | | 28 | Documentation | help | documentation | 7 | | 29 | About | help | about | 7 | | 30 | Products | products | product | 17 | | 31 | Categories | categories | category | 17 |

Demostración SQL Fiddle. He insertado algunos datos de muestra.

Challange

Necesito encontrar todos los padres del registro donde el título es Categories . ¿Cómo puedo obtener todos los padres con solo una consulta?
Quiero decir que necesito este resultado:

Salida deseada

id | title | controller | method | url | parent_id ---------------------------------------------------------------- 3 | Modules | admin | modules | (NULL) | 0 17 | User Modules | modules | user_module | (NULL) | 3 31 | Categories | categories | category | (NULL) | 17

Supongamos que quiero buscar una entrada con todos sus elementos principales y quiero usar la condición where id = 31 , debe buscar los registros anteriores.


SELECT T2.id, T2.title,T2.controller,T2.method,T2.url FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 31, @l := 0) vars, menu m WHERE @r <> 0) T1 JOIN menu T2 ON T1._id = T2.id ORDER BY T1.lvl DESC;

Demo