Extract all rows of a column based on the value of another column (SQL query)

Question

I have table

a | b 
----- 
1 | 3 
3 | 2 
3 | 4 
2 | 5 
3 | 6 
2 | 7

how to write sql query if a = 1 then result 3 2 4 5 6 7, if a = 3 then 2 4 5 6 7, if 2 then 5 7 here is my query

select * 
from table
where a in (select b from table where a = 1) or a = 1

but the result only 3 2 4 6 because 3 has 2 in col b so i want also to have 5 7 thanks

Answer 1

I suspect that you have a hierarchical structure, where a is the parent and b is the child, and that you are looking for all descendents of a given node.

One common way to walk such structure is a hierarchical query. In SQL Server:

with cte as (
    select a, b from mytable where a = @your_parameter
    union all
    select t.a, t.b from mytable t inner join cte c on t.a = c.b 
)
select * from cte

Demo on DB Fiddle - when given 3 as parameter:

 a |  b
-: | -:
 3 |  2
 3 |  4
 3 |  6
 2 |  5
 2 |  7