Mysql check if row has child records in one query

Question

Hello here is my table structure

I am trying to determine does record with ID 1 has child records (parent_id column ), and this should work for all record not just for specific one

Trying something like this

SELECT id, ??? as 'has_child_rows' FROM image_tree WHERE parent_id IS NULL

Any idea left join, recursion, subquery?

Answer 1

You can use exists and an inline correlated subquery:

select 
    id,
    exists(select 1 from image_tree t1 where t1.parent_id = t.id) has_child_rows
from image_tree t

This returns all the rows in the table, with a boolean value that indicates whether a child record exists.

To filter on the record that has id = 1, just add a where clause:

select 
    id,
    exists(select 1 from image_tree t1 where t1.parent_id = t.id) has_child_rows
from image_tree t
where id = 1