MySQL - Displaying a record and its parents

Question

How to display a specific record and its parents (maximum two)?

Table:
+---------------+
| id | parentID |
+---------------+
|  1 |     null |
|  2 |        1 |
|  3 |        1 |
|  4 |        2 |
|  5 |        2 |
|  6 |        2 |
+---------------+

The expected result:
Showing record with id 4 and his two parents

+---------------+
| id | parentID |
+---------------+
|  1 |     null |
|  2 |        1 |
|  4 |        2 |
+---------------+

I don't know how to do it. The following query doesn't work.

(SELECT `c1`.`parentID` FROM `table` as `c1` WHERE `c1`.`id` = 4 LIMIT 1) AS `c1parentID`,
(SELECT `c2`.`parentID` FROM `table` as `c2` WHERE `c2`.`id` = `c1parentID` LIMIT 1) AS `c2parentID`,
SELECT * FROM `table` as `c3` WHERE `c3`.`id` = `c1parentID` OR `c3`.`id` = `c2parentID` OR `c3`.`id` = 4

My knowledge of advanced queries is very poor. Please help :)

I don't understand exactly what you want to do, this query will give you result you want to achive if the last table is result you want to get http://sqlfiddle.com/#!9/ab531/1 — Aleksandar Miladinovic, Apr 25 '15 at 14:03
I don't get it how record with id 4 can have two parents with this table structure? — Aleksandar Miladinovic, Apr 25 '15 at 14:11

score 0 · Accepted Answer · answered Apr 25 '15 at 14:03

0

You will need CURSOR and LOOP. You will be looping from the child all the way up to the root (until parentId is NULL).

http://www.mysqltutorial.org/mysql-cursor/

answered Apr 25 '15 at 14:03

Riad Baghbanli

3,105
1
12
20

MySQL - Displaying a record and its parents

1 Answers1