SQL – return a value referenced by initial value from same table

I’m sure this has been asked before, but I can’t seem to find any questions that help.

Here’s an example of a table:

ID    Name        Parent ID
---------------------------
1     Ice cream   3 
2     Chips       4
3     Sweets      null
  

I’m trying to figure out how to write a single query which, given ID=1, will return me rows 1 and 3. Is this possible without making two queries?

Also, is there a way to return the information of the parent as a custom column? So, rather than returning 2 rows, returning the row where id=1 with parent_id=3 added on?

Answer

You can use union all and exists:

select * from mytable where parent_id = 3
union all
select t.* 
from mytable t 
where exists (select 1 from mytable t1 where t1.parent_id = t.id and t.parent_id = 3)

If you want to do this over multiple levels of hierarchy, then you would typically use a recursive query. The syntax slightly varies accross databases (and not all of them support recursion), but the idea is:

with recursive cte as (
    select * from mytable where parent_id = 3
    union all
    select t.*
    from cte c
    inner join mytable t on t.parent_id = c.id
)
select * from cte