SQLite - Counting referenced rows in one table using a different, related self-referencing table
Test

WITH RECURSIVE cte AS
(
SELECT id, guid, parent_id, id _id_, guid _guid_, parent_id _parent_id_
FROM metadata_items
UNION ALL
SELECT cte.id, cte.guid, cte.parent_id, src.id, src.guid, src.parent_id
FROM cte
JOIN metadata_items src ON cte._parent_id_ = src.id
)
SELECT cte.id, cte.guid, cte.parent_id, MAX(target_metadata_item_id) IS NOT NULL 
FROM cte 
LEFT JOIN media_subscriptions src ON cte._id_ = src.target_metadata_item_id
GROUP BY cte.id, cte.guid, cte.parent_id
ORDER BY cte.id;


fiddle

If you need the data for one separate record only then add proper WHERE.