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

Question

media_subscriptions
+----+--------------------------+
| id | target_metadata_item_id |
+----+--------------------------+
| 30 | 19039 |
| 31 | NULL |
| 32 | NULL |
| 33 | NULL |
| 34 | NULL |
| 35 | NULL |
| 36 | NULL |
| 37 | 18976 |
+----+--------------------------+
metadata_items
+-------+-----------------+-----------+
| id | guid | parent_id |
+-------+-----------------+-----------+
| 18976 | id://265 | NULL |
| 18977 | id://265/1 | 18976 |
| 19039 | id://841 | NULL |
| 19040 | id://841/1 | 19039 |
| 19041 | id://841/1/1 | 19040 |
| 19224 | id://841/1/2 | 19040 |
| 19966 | id://265/1/1 | 18977 |
| 19967 | id://290 | NULL |
| 19968 | id://376 | NULL |
+-------+-----------------+-----------+

I have the above two tables with data. The parent_id column defines how each row in metadata_items relates to any other row, which is also evidential by each row's guid. The parent_id can be nested by a root ID having a child, and that child having a child (if that makes sense). The target_metadata_item_id column from media_subscriptions relates to the id column of the metadata_items table. I want to get a COUNT(*) of any records from the metadata_items table that have an associated record in media_subscriptions based on a search of guid. Ultimately, if there is a matching record, the count should only ever be 1, and 0 if not, but I'm using COUNT(*) just in case there's ever a duplicate record in the media_subscriptions table, and then I'm just checking elsewhere if it returns > 0.

Some examples of what I'm trying to accomplish:

• guid='id://841' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
• guid='id://841/1/1' would return 1 because its root parent id (19039) is referenced by id 30 in media_subscriptions.
• guid='id://290' would return 0 because its root parent id (19967) is NOT referenced by ANY column in media_subscriptions.

Here's what I've got right now, which obviously isn't working as I'd intend (take it easy on me, I'm a noob with database administration, and am just starting to learn).

SELECT COUNT(*) FROM `metadata_items` AS `t1`
 LEFT JOIN `metadata_items` AS `t2` ON t1.id = t2.parent_id
 INNER JOIN `media_subscriptions` AS `subscriptions` ON t1.id = subscriptions.target_metadata_item_id
 WHERE t1.guid='id://841'

This correctly returns 1, but if I switch out the last line with WHERE t1.guid='id://265/1/1' it returns 0 where I'd like it to return 1. Obviously I'm doing this very incorrectly, but I'm really not sure how to go about getting where I need to be.

• Akina: Is it possible that intermediate (not root) item node is referenced by a subscription?
• StartHere Software: No, only a root item node will ever be referenced by a subscription.

Answer 1

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.

• StartHere Software: That does does the trick! I only ever need to check one record at a time, so adding the WHERE clause does exactly what I need it to do. :-)