SELECT
	shift_assignments.shift_date, 
	tsa.room_id, 
	floorwise_room.room_name, 
	tsa.bed_id, 
	IFNULL(bed.bed_name, '---') AS bed_name, 
	dts.taskid, 
	dts.tasktype, 
	dts.taskname, 
	shift_assignments.shift_code
FROM
	task_staff_assignments AS tsa
	INNER JOIN
	shift_assignments
	ON 
		tsa.shift_assignment_id = shift_assignments.id
	INNER JOIN
	floorwise_room
	ON 
		tsa.room_id = floorwise_room.id
	INNER JOIN
	(
		SELECT
			'T' AS tasktype, 
			dt.id AS taskid, 
			dt.task_name AS taskname
		FROM
			daily_tasks AS dt
			WHERE dt.id not in (SELECT ds.daily_task_id FROM daily_subtasks AS ds)
				
		UNION
		
		SELECT
			'S' AS tasktype, 
			ds.id AS taskid, 
			CONCAT( dt.task_name, '/', ds.subtask_name ) AS taskname
		FROM
			daily_tasks AS dt
			INNER JOIN
			daily_subtasks AS ds
			ON 
				dt.id = ds.daily_task_id
	) AS dts
	ON 
		dts.tasktype = tsa.is_task_or_subtask AND
		dts.taskid = tsa.daily_subtask_id
	LEFT JOIN
	bed
	ON 
		tsa.bed_id = bed.id
WHERE
	shift_assignments.shift_date = '2021-12-14' AND
shift_assignments.staff_id = 156