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?