By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `contingents` (
`id` int(11) NOT NULL,
`daily` int(11) DEFAULT NULL,
`daily_remain` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `contingents` (`id`, `daily`, `daily_remain`, `parent_id`)
VALUES
(1,10,10,NULL),
(2,10,5,1),
(3,10,NULL,2),
(4,10,0,NULL),
(5,10,10,4);
CREATE TABLE `vendors` (
`id` int(11) NOT NULL,
`contingent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `vendors` (`id`, `contingent_id`)
VALUES
(1,3),
(2,5);
SELECT * FROM contingents;
SELECT * FROM vendors;
Records: 5 Duplicates: 0 Warnings: 0
Records: 2 Duplicates: 0 Warnings: 0
| id | daily | daily_remain | parent_id |
|---|---|---|---|
| 1 | 10 | 10 | null |
| 2 | 10 | 5 | 1 |
| 3 | 10 | null | 2 |
| 4 | 10 | 0 | null |
| 5 | 10 | 10 | 4 |
| id | contingent_id |
|---|---|
| 1 | 3 |
| 2 | 5 |
WITH RECURSIVE
cte AS (
SELECT ven.id,
CASE WHEN con.daily_remain = 0 THEN 1 ELSE 0 END have_zero,
con.parent_id
FROM vendors ven
JOIN contingents con ON ven.contingent_id = con.id
UNION ALL
SELECT cte.id,
CASE WHEN con.daily_remain = 0 THEN 1 ELSE 0 END,
con.parent_id
FROM cte
JOIN contingents con ON cte.parent_id = con.id
WHERE NOT have_zero
)
SELECT id, NOT MAX(have_zero) contingent_left
FROM cte
GROUP BY id
| id | contingent_left |
|---|---|
| 1 | 1 |
| 2 | 0 |