What is the correct way to join two complex mysql select statements?

Question

I am able to pull out two lists from my tables. One shows all the units for each student in each cohort. Another shows if all the parts of each unit have been submitted, for a particular student's work in a specific cohort. I want to join the lists, so that I can see who has submitted (or not) each part of each unit, for each student in each cohort.

cohort_units:
cohort_id    unit    part
235          ABC     A
235          ABC     B
246          DEF     A
246          DEF     B
246          DEF     C

cohort_students:
user_id      cohort_id
21           235
24           235
43           235
53           246

assignments:
user_id    cohort_id    unit     draft1recdt 
21         235          ABCA     2023-01-03
21         235          ABCB     NULL
24         235          ABCA     2023-02-01
24         235          ABCB     2023-02-02  

This pulls a list of units with the user id and cohort id.

SELECT cohort_students.user_id,
       cohort_units.unit,
       cohort_units.cohort_id 
FROM cohort_units 
LEFT JOIN cohort_students 
    ON cohort_units.cohort_id = cohort_students.cohort_id 
GROUP BY cohort_units.unit,cohort_students.user_id 
ORDER BY cohort_students.user_id;

result:
user_id    unit    cohort_id
21         ABC    235
24         ABC    235
43         ABC    235
53         DEF    236

This returns a row IF there are more parts to an assignment than parts that have been submitted, for each unit that each student in each cohort should have completed given the cohort id, user id and unit name.

SELECT GROUP_CONCAT(CASE WHEN draft1recdt IS NOT NULL THEN draft1recdt END) AS drafts,
       (LENGTH(GROUP_CONCAT(DISTINCT draft1recdt))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT draft1recdt), ',', '')))+1 as numDrafts,
       cohort_units.unit,
       GROUP_CONCAT(cohort_units.part) as parts,
       (LENGTH(GROUP_CONCAT(DISTINCT cohort_units.part))-LENGTH(REPLACE(GROUP_CONCAT(DISTINCT cohort_units.part), ',', '')))+1 as numParts 
FROM assignments 
LEFT JOIN cohort_units 
    ON assignments.cohort_id = cohort_units.cohort_id 
    AND assignments.unit = CONCAT(cohort_units.unit,cohort_units.part) 
WHERE assignments.cohort_id = 235 
    AND cohort_units.unit = 'ABC' AND assignments.user_id = 21 
GROUP BY cohort_units.unit 
HAVING numParts > numDrafts;

How do I make the second select statement part of the first, using the three columns on the first select statement as the joining information?

I want to run the second query on every result from the first query. Using the data above, I would expect to pull out user id 21 as they have only submitted one part of a two part unit.

user_id    unit   cohort_id   parts   numParts   numDrafts
21         ABC    235         A,B     2          1

Is this a JOIN? Or a SUBQUERY?

Answer 1

(For what it's worth, I believe cohort is an accepted term in various social science disciplines.)

Your problem would be made easier if your assignments table had a part column matching the part table in your cohort_units table. So let's start with a subquery to generate a virtual table with that column present.

SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 
     ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part)

We'll use this subquery in place of assignments moving forward. This is a bit of a kludge, but it will make later work cleaner.

Next we need the number of parts in each unit. That's a simple aggregate:

SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit

We can organize our query using common table expressions, like so.

WITH completed AS (
 SELECT assignments.user_id, assignments.cohort_id, 
       cohort_units.unit, cohort_units.part,
       assignments.draft1recdt
  FROM assignments
  JOIN cohort_units 

      ON assignments.unit = CONCAT(cohort_units.unit, cohort_units.part) 
),
partcount AS (
SELECT COUNT(*) num_parts,
       cohort_id,
       unit
  FROM cohort_units
 GROUP BY cohort_id, unit
)
SELECT completed.user_id, cohort_units.cohort_id, cohort_units.unit, 
       GROUP_CONCAT(completed.part) parts,
       COUNT(*) completed_parts,
       partcount.num_parts
  FROM cohort_units
  JOIN partcount
         ON cohort_units.cohort_id = partcount.cohort_id
        AND cohort_units.unit = partcount.unit
  JOIN completed
         ON completed.cohort_id = cohort_units.cohort_id
        AND completed.unit = cohort_units.unit
        AND completed.part = cohort_units.part
GROUP BY completed.user_id, cohort_units.cohort_id, cohort_units.unit, num_parts
HAVING COUNT(*) < partcount.num_parts

Here's a fiddle. https://dbfiddle.uk/FvGkiAnl

One of the tricks to this is the separate aggregate to get the part counts.