How to get children and parent based on a value that the parent has

Question

Let say we have this table:

+─────+────────────+───────+
| id  | parent_id  | code  |
+─────+────────────+───────+
| 1   |            | A     |
| 2   | 1          |       |
| 3   | 1          |       |
| 4   |            | B     |
+─────+────────────+───────+

I need to search for records that have code "A" and those which their parent have code "A" as well. In this case, the query result should return records with id: 1, 2 and 3.

Why those 3?

id 1: has code "A".
id 2: its parent 1 has code "A".
id 3: its parent 1 has code "A".

Any idea will be really appreciated. Thanks.

Does this answer your question? [MySQL Recursive get all child from parent](https://stackoverflow.com/questions/41913460/mysql-recursive-get-all-child-from-parent) — Raymond Nijland, Jan 18 '22 at 08:54

score 1 · Answer 1 · edited Jan 18 '22 at 09:29

1

You can fix it with something like this

SELECT id 
FROM tableName 
WHERE code = "A" OR parent_id IN (SELECT id FROM tableName WHERE code = "A")

edited Jan 18 '22 at 09:29

Elikill58

4,050
24
23
45

answered Jan 18 '22 at 08:04

Arxeus

62
6

score 0 · Answer 2 · answered Jan 18 '22 at 08:04

0

SELECT t1.*
FROM table t1
JOIN table t2 ON t2.id IN (t1.id, t1.parent_id)
WHERE t2.code = 'A'

answered Jan 18 '22 at 08:04

Akina

39,301
5
14
25

How to get children and parent based on a value that the parent has

2 Answers2