Mysql, Show who has and hasn't opted out of bus service-CodePudding

I have student and event tables linked by 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, '');

The student can select not to get bus service, which shows as an entry (like eventid 43 above). I can get the list of 'bus students', along with an id to show who cancelled service and who hasn't.

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' ;

However, when I try to limit where with the date, the result shows only the one who cancelled service.

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%';

How can I add this limiter and get the full results like the first query? Thanks in advance for your help.

CodePudding user response：

You may move the restriction on the event date to the ON clause of the left join:

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

For students who did not cancel service, the bid would be reported as null.

CodePudding user response：

You want to show all students that use a bus servvice, but exclude those that opt out for a certain date. You can use NOT EXISTS or NOT IN to do this lookup.

SELECT *
FROM `students`
WHERE bus LIKE 'Bus%' AND active = 'Yes'
AND sid NOT IN
(
  SELECT sid
  FROM `STATevent`
  WHERE DATE(date) = DATE '2022-11-30'
)
ORDER BY sid;

If you want to show all potential bus service users instead with a flag whether they attend that day or not, you can move the condition to the select clause:

SELECT s.*
  sid IN
  (
    SELECT sid
    FROM `STATevent`
    WHERE DATE(date) = DATE '2022-11-30'
  ) AS opt_out
FROM `students` s
WHERE bus LIKE 'Bus%' AND active = 'Yes'
ORDER BY sid;

Demo: https://dbfiddle.uk/TxyzF564