Track stepwise project completion

from the Artful Common Queries page

A master table has one row for each project, and the number of sequential steps required to complete each project. A detail table has one row per project per completed step: 
DROP TABLE IF EXISTS t1 ;
CREATE TABLE t1 (
  id INT, projectname CHAR(2), projectsteps INT
);
INSERT INTO t1 VALUES 
(1, 'xx', 3), 
(2, 'yy', 3),
(3, 'zz', 5);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  id INT, projectID INT, xid INT
);
INSERT INTO t2 VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 1, 3),
(5, 3, 2),
(6, 1, 2),
(7, 2, 1),
(8, 2, 1);
The requirement is for a query which, for every project, reports 'OK' if there is at least one detail row for every project step, or otherwise reports the number of the last sequential completed step:

Here is one way to build such a query:

1. Join t1 to t2 on projectID.

2. Left Join t2 to itself on projectID and integer succession.

3. Add a WHERE condition which turns the left self-join into an exclusion join that finds the first missing sequential xid value.

4. To the SELECT list add this item: 

  IF( a.xid < p.projectstep,a.xid,'OK' ) AS StepState
so when the first sequential missing xid is not less than the number of project steps, display 'Ok', otherwise display the xid value before the first missing xid value. 5. Remove dupes with a GROUP BY clause. 
SELECT 
  p.projectname,p.projectsteps,a.xid,
  IF(a.xid < p.projectsteps, a.xid, 'OK') AS CompletionState
FROM t1 p
JOIN t2 a ON p.id = a.projectID
LEFT JOIN t2 AS b ON a.projectID = b.projectID AND a.xid+1 = b.xid
WHERE b.xid IS NULL 
GROUP BY p.projectname;
+-------------+--------------+------+-----------------+
| projectname | projectsteps | xid  | CompletionState |
+-------------+--------------+------+-----------------+
| xx          |            3 |    3 | OK              |
| yy          |            3 |    1 | 1               |
| zz          |            5 |    2 | 2               |
+-------------+--------------+------+-----------------+

Last updated 17 Aug 2024