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

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.

CodePudding user response：

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")

CodePudding user response：

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

CodePudding user response：

You can use use a union between two queries that get rows with your two conditions:

select id from table_name where code = 'A'
union all
select id from table_name where parent_id in 
(select id from table_name where code = 'A')