Question 22018 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search