MariaDB SQL Recursive query as subquery

Question

I have a data structure where:

• a vendor has a contingent (multiple vendors can use the same contingent, that's why it is different tables)
• A contingent can also have a parent contingent (unlimited in depth)
• If one of the contingents in the chain has nothing left, the vendor is considered to no contingent left
• If the remains are NULL, there is no limit set, and it counts as contingent available

A redesign of the data structure to solve this, sadly, is not possible.

The recursive part and all contigent-avalible rules cann be coded like this:

WITH RECURSIVE rec_contigents as (
  SELECT 
    id, IFNULL(daily_remain, 1) > 0 as row_contigent, parent_id
  FROM contingents
  Where id = 1
  UNION
  select pc.id, IFNULL(pc.daily_remain, 1) > 0 as row_contigent, pc.parent_id
  FROM contingents pc, rec_contigents c
  where pc.id = c.parent_id)
select id, SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents;

I can easily get all my vendors, and then query the above for them one by one. Though, I thought let's reduce Database calls, and use the above as a subquery. (simplified, in reality this contains multiple JOINS on Vendor and some where-clauses)

SELECT
  v.vendor_id,
  (
        WITH RECURSIVE rec_contigents as (
            SELECT 
            id, IFNULL(daily_remain, 1) > 0 as row_contigent, parent_id
            FROM contingents
            Where id = v.contingent_id
            UNION
            select pc.id, IFNULL(pc.daily_remain, 1) > 0 as row_contigent, pc.parent_id
            FROM contingents pc, rec_contigents c
            where pc.id = c.parent_id
        )
        select SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents
    ) as contingent_left
  FROM vendors AS v

This results in the error: Unknown column 'v.contingent_id' in 'where clause'.

Following example data:

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);

Expected outcome:

+-----------+-----------------+
| vendor_id | contingent_left |
+-----------+-----------------+
|     1     |        1        |
|     2     |        0        |
+-----------+-----------------+

Vendor 1: Has Contingent, because he and all his parents all have Continent left (or have unlimited contingent). Vendor 2: Does not have continent, because its parent does not have continent left.

I have already looked at the following posts, though these solutions don't work for me:

• Mysql Mariadb recursive query - uses a JOIN on the recursive table, that I can't use as I first need to sum it
• SQL Recursive query as subquery - says to remove the Where id = v.contingent_id which results in to much data and the select SUM(row_contigent) = count(row_contigent) as contigent from rec_contigents resulting in the wrong result

Answer 1

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 * FROM cte
SELECT id, NOT MAX(have_zero) contingent_left
FROM cte
GROUP BY id

https://dbfiddle.uk/wVJDsp4V