I have this table
| Id | ParentId |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | 1 |
| 4 | NULL |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
Each child (Id) can have only one parent.
How to retrieve parents and sibling for a given child Id, it means:
SELECT `Here the code`
WHERE Id = 2
Expected result
| Id | ParentId |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | 1 |
For sure I can do something like
select Id, Parentid
from MyTable
where id = (select ParentId from MyTable where id = 2)
or ParentId = (select ParentId from MyTable where id = 2)
but it’s not very convenient for the rest of my query.
I can also use variables but not convenient too.
I was thinking about recursive query but for the moment without success.
Thanks.
7