MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE assignments(
   ASSIGNMENT_ID   INTEGER  NOT NULL
  ,ASSIGNMENT_NAME VARCHAR(17) NOT NULL
);
INSERT INTO assignments(ASSIGNMENT_ID,ASSIGNMENT_NAME) VALUES (1,'Morning Tasks');
INSERT INTO assignments(ASSIGNMENT_ID,ASSIGNMENT_NAME) VALUES (2,'Afternoon Tasks');
INSERT INTO assignments(ASSIGNMENT_ID,ASSIGNMENT_NAME) VALUES (3,'Evening Tasks');
CREATE TABLE tasks(
   TASK_ID       INTEGER  NOT NULL
  ,ASSIGNMENT_ID INTEGER  NOT NULL
  ,TASK_NAME     VARCHAR(16) NOT NULL
  ,TASK_STATUS   INTEGER  NOT NULL
);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (1,1,'Make Bed',2);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (2,1,'Brush Teeth',2);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (3,1,'Eat Breakfast',2);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (4,1,'Commute',2);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (5,2,'Eat Lunch',2);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (5,2,'Wash Dishes',1);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (6,2,'Wash Table',1);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (7,3,'Make Supper',1);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (8,3,'Eat Supper',1);
INSERT INTO tasks(TASK_ID,ASSIGNMENT_ID,TASK_NAME,TASK_STATUS) VALUES (9,3,'Wash Dishes',1);
select count(*)
from assignments a
where not exists (
    select 1
    from tasks t
    where 
        t.assignment_id = a.assignment_id
        and t.task_status != 2
)


count(*)


1


select *
from assignments a
where not exists (
    select 1
    from tasks t
    where 
        t.assignment_id = a.assignment_id
        and t.task_status != 2
)


ASSIGNMENT_ID
ASSIGNMENT_NAME


1
Morning Tasks