Mysql，显示谁已经和没有 Select 退出巴士服务

我有由SID链接的学生和事件表.

 CREATE TABLE `students` (
`sid` int(8) NOT NULL COMMENT 'use',
`active` enum('Yes','No','vac','Grad') NOT NULL DEFAULT 'Yes',
`name` varchar(130) DEFAULT NULL,
`bus` varchar(130) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `students` (`sid`, `LEFT(name, 2)`, `bus`) VALUES
(51, 'Me', 'BusA'),
(52, 'Hi', 'BusA'),
(59, 'An', 'BusA'),
(70, 'Mi', 'BusB'),
(100, 'Yu', 'BusB');

CREATE TABLE `STATevent` (
  `eventid` int(24) NOT NULL,
  `sid` int(4) NOT NULL,
  `date` datetime NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `level` enum('absent','bus') CHARACTER SET utf8 NOT NULL,
  `color` varchar(10) NOT NULL,
  `Percent` tinyint(5) NOT NULL,
  `note` varchar(266) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `STATevent` (`eventid`, `sid`, `date`, `created`, `level`, `color`, `Percent`, `note`) VALUES
(43, 59, '2022-11-30 21:17:04', '2022-11-28 12:17:04', 'bus', 'red', 100, '');

学生可以 Select 不使用公共汽车服务，它显示为一个条目(如上面的EventID43).我可以拿到"公交学生"的名单，还有一个显示谁取消了服务，谁没有取消服务的身份证.

SELECT C.name, C.sid, O.sid AS 'bid', C.bus FROM students C
LEFT JOIN STATevent O ON  C.sid = O.sid
WHERE C.bus LIKE 'Bus%' AND C.active = 'Yes' ;

然而，当我try 将日期限制为where时，结果只显示了取消服务的那个.

SELECT C.name, C.sid, O.sid AS 'bid', C.bus FROM students C
LEFT JOIN STATevent O ON  C.sid = O.sid
WHERE C.bus LIKE 'Bus%' AND C.active = 'Yes' AND O.date LIKE '2022-11-29%';

如何添加此限制器并获得与第一个查询一样的完整结果？ 首先要感谢你的帮助.