MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Tasks
	(`TASK_ID` int, `ASSIGNMENT_ID` int, `TASK_NAME` varchar(13), `TASK_STATUS` int)
;
	
INSERT INTO Tasks
	(`TASK_ID`, `ASSIGNMENT_ID`, `TASK_NAME`, `TASK_STATUS`)
VALUES
	(NULL, NULL, NULL, NULL),
	(1, 1, 'Make Bed', 2),
	(NULL, NULL, NULL, NULL),
	(2, 1, 'Brush Teeth', 2),
	(NULL, NULL, NULL, NULL),
	(3, 1, 'Eat Breakfast', 2),
	(NULL, NULL, NULL, NULL),
	(4, 1, 'Commute', 2),
	(NULL, NULL, NULL, NULL),
	(5, 2, 'Eat Lunch', 2),
	(NULL, NULL, NULL, NULL),
	(5, 2, 'Wash Dishes', 1),
	(NULL, NULL, NULL, NULL),
	(6, 2, 'Wash Table', 1),
	(NULL, NULL, NULL, NULL),
	(7, 3, 'Make Supper', 1),
	(NULL, NULL, NULL, NULL),
	(8, 3, 'Eat Supper', 1),
	(NULL, NULL, NULL, NULL),
	(9, 3, 'Wash Dishes', 1)
;
CREATE TABLE Assignments
	(`ASSIGNMENT_ID` int, `ASSIGNMENT_NAME` varchar(15))
;
	
INSERT INTO Assignments
	(`ASSIGNMENT_ID`, `ASSIGNMENT_NAME`)
VALUES
	(NULL, NULL),
	(1, 'Morning Tasks'),
	(NULL, NULL),
	(2, 'Afternoon Tasks'),
	(NULL, NULL),
	(3, 'Evening Tasks')
;
    select 
        ASSIGNMENT_ID 
    from Tasks
    group by ASSIGNMENT_ID
    having count(1) = count(case when TASK_STATUS = '2' then 1 end)


ASSIGNMENT_ID


1


select * 
from Assignments
where ASSIGNMENT_ID  in (
    select 
        ASSIGNMENT_ID 
    from Tasks
    group by ASSIGNMENT_ID
    having count(1) = count(case when TASK_STATUS = '2' then 1 end)
)


ASSIGNMENT_ID
ASSIGNMENT_NAME


1
Morning Tasks