Suppose I have a parent table (t_parent) like this:

Suppose I have a parent table (t_parent) like this:

And a children table (t_children) like this:

How can I query all the parent records with missing children records matching the t_parent.max_children_key and t_children.sort_key? Base from this two dataset, the query should return:

• Parent 2 - Because Child 2 and Child 3 are missing.
• Parent 4 - Because Child 2 to Child 7 are missing.
• Parent 5 - Because Child 1 to Child 6 are missing.
• Parent 6 - Because despite having 2 children, Child 1 is missing.
• Parent 7 - Because despite having 2 children, Child 1 and Child 2 are missing.

Right now, I'm matching the count of the children using having count(1) but it fails to include Parent 6 and Parent 7.

• sql
• mysql

3 Answers 3

SELECT *
FROM t_parent tp
WHERE EXISTS (
    SELECT 1
    FROM t_children tc
    WHERE tc.PARENT_ID = tp.ID
    HAVING 
        COUNT(DISTINCT tc.SORT_KEY) <> tp.MAX_CHILDREN_KEY
        OR MAX(tc.SORT_KEY) <> tp.MAX_CHILDREN_KEY
);

This query compares MAX_CHILDREN_KEY with the maximum value of SORT_KEY and the number of its unique values ​​for each PARENT_ID.

db<>fiddle

you can try the following, you can set limits according to your needs:

WITH NumberSeries AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 
    FROM NumberSeries
    WHERE num < 10  -- Set a reasonable upper limit 
),
ExpectedChildren AS (
    SELECT p.ID AS PARENT_ID, ns.num AS EXPECTED_SORT_KEY
    FROM t_parent p
    CROSS JOIN NumberSeries ns
    WHERE ns.num <= p.MAX_CHILDREN_KEY
),
MissingChildren AS (
    SELECT ec.PARENT_ID, ec.EXPECTED_SORT_KEY
    FROM ExpectedChildren ec
    LEFT JOIN t_children c
        ON ec.PARENT_ID = c.PARENT_ID AND ec.EXPECTED_SORT_KEY = c.SORT_KEY
    WHERE c.ID IS NULL
)
SELECT DISTINCT p.ID, p.NAME
FROM MissingChildren mc
JOIN t_parent p ON mc.PARENT_ID = p.ID
ORDER BY p.ID
OPTION (MAXRECURSION 100);

Detailed form:

WITH RECURSIVE
cte (ID, NAME, CUR_CHILDREN_KEY, MAX_CHILDREN_KEY) AS (
  SELECT ID, NAME, 1, MAX_CHILDREN_KEY
  FROM t_parent
  UNION ALL
  SELECT ID, NAME, 1 + CUR_CHILDREN_KEY, MAX_CHILDREN_KEY
  FROM cte 
  WHERE CUR_CHILDREN_KEY < MAX_CHILDREN_KEY
)
SELECT t1.ID, t1.NAME, t1.CUR_CHILDREN_KEY LOST_CHILDREN_KEY
FROM cte t1
LEFT JOIN t_children t2 
  ON (t1.ID, t1.CUR_CHILDREN_KEY) = (t2.PARENT_ID, t2.SORT_KEY)
WHERE t2.PARENT_ID IS NULL
ORDER BY ID, LOST_CHILDREN_KEY

Compact form:

WITH RECURSIVE
cte (ID, NAME, CUR_CHILDREN_KEY, MAX_CHILDREN_KEY) AS (
  SELECT ID, NAME, 1, MAX_CHILDREN_KEY
  FROM t_parent
  UNION ALL
  SELECT ID, NAME, 1 + CUR_CHILDREN_KEY, MAX_CHILDREN_KEY
  FROM cte 
  WHERE CUR_CHILDREN_KEY < MAX_CHILDREN_KEY
)
SELECT t1.ID, t1.NAME, 
       COUNT(*) LOST_CHILDREN_AMOUNT,
       GROUP_CONCAT(t1.CUR_CHILDREN_KEY ORDER BY t1.CUR_CHILDREN_KEY) LOST_CHILDREN_KEYS
FROM cte t1
LEFT JOIN t_children t2 
  ON (t1.ID, t1.CUR_CHILDREN_KEY) = (t2.PARENT_ID, t2.SORT_KEY)
WHERE t2.PARENT_ID IS NULL
GROUP BY t1.ID, t1.NAME
ORDER BY ID

fiddle

PS. Thanks to id'7238 for source sample data.

PPS. The query does not check for child rows whose SORT_KEY value is greater than MAX_CHILDREN_KEY value for its parent (or less than 1).