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

Question

I can take two lists out of my table. All units for each student in each cohort are shown. The other shows whether all sections of each unit have been submitted for work for a specific student in a specific group. I would like to join the list so that I can view the submissions (or non-submissions) for each section 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 will extract a list of organizations with user IDs and group IDs.

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

If the assignment has more parts than have been submitted, this operation will return one row for each unit that each student in each queue should complete given the queue 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 to use the three columns on the first select statement as connection information to make the second select statement become part of the first select statement?

I want to run a second query on every result of the first query. Using the data above, I wish to extract user ID 21 since they only submitted part of the two-part unit.

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

Is this a join? Or subquery?

Answer 1

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

If your assignments table has a part column that matches your cohort_unitspart table, your problem will become Easier Code > Table. So let's start with a subquery and generate a dummy table containing the column.

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 will use this subquery instead of assignment. This is a bit messy, but it will make future work clearer.

Next we need the number of parts in each unit. This is a simple aggregation:

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

We can use common table expressions to organize queries as shown below.

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

This is a fiddle. https://dbfiddle.uk/FvGkiAnl

One of the tricks to achieve this is to aggregate individually to get the part count.