Getting parental status from a self-referencing table

Question

Let's say you have the following table: items(item_id, item_parent) ... and it is a self-referencing table as item_parent refers to item_id.

What MySQL supported SQL query would you use to SELECT each item in the table along with a boolean value that indicates whether that item is a parent / has other items referencing to it?

If you have the following data in the table:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

... the query should be able to retrieve the following set of objects:

{"item_id":1,"is_parent":0}
{"item_id":2,"is_parent":1}
{"item_id":3,"is_parent":1}
{"item_id":4,"is_parent":0}
{"item_id":5,"is_parent":0}

Answer 1

SELECT  i.item_id,
        item_id IN
        (
        SELECT  item_parent
        FROM    items
        )
FROM    items i

Answer 2

This returns all items and an integer specifying how many children each has:

SELECT T1.item_id, COUNT(T2.item_parent) AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id

If you want a boolean (0 or 1) just change it to:

SELECT T1.item_id, COUNT(T2.item_parent) > 0 AS is_parent
FROM items AS T1
LEFT JOIN items AS T2
ON T1.item_id = T2.item_parent
GROUP BY item_id