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

MySQL: How to combine result from COUNT subquery with an EXCEPT query

I have 2 tables…

  1. crew_availability (ca) which has id(INT), admin_id(INT) and usable_date(DATE) fields.
  2. reservations (r) which has id(INT), ride_id(INT), admin_id(INT) and ride_date(DATE) fields.

The following EXCEPT query provides expected result in that it gives remaining crew availability.

SELECT ca.admin_id, ca.usable_date FROM crew_availability ca EXCEPT SELECT r.admin_id, r.ride_date FROM  reservations r;

However, the problem arises in that more than one ride (ride_id field in reservations table) exists for any one crew member per day. This of course means that admin_id/ride_date are not unique in the reservations table and even if only one of a crew member’s rides has a reservation for a given date, their remaining availability for any other rides that day is no longer reflected in the result of the EXCEPT query.

I’ve tried to resolve this by counting the number of rides a crew member has on a given day and if less than the total possible, to get their remaining availability still reflected in the main EXCEPT query result e.g.

SELECT ca.admin_id, ca.usable_date FROM crew_availability ca EXCEPT SELECT r.admin_id, r.ride_date FROM  reservations r
  WHERE (SELECT COUNT(id) FROM reservations r WHERE r.admin_id = 1 AND r.ride_date = '2023-04-10' < 2);

This might be syntactically viable but it doesn’t work… perhaps it serves to outline what I’m trying to achieve though.

Any help with trying to achieve a correct representation for availability would be genuinely appreciated, thanks

crew_availability SQL:

SET NAMES 'utf8mb4';

INSERT INTO `bike-data`.crew_availability(id, admin_id, usable_date, created_at, updated_at) VALUES
(42, 28, '2023-04-03', '2022-08-21 15:43:45', '2022-08-21 15:43:45'),
(50, 1, '2023-04-03', '2022-08-21 17:02:51', '2022-08-21 17:02:51'),
(53, 1, '2023-04-10', '2022-08-21 17:59:58', '2022-08-21 17:59:58'),
(55, 1, '2023-04-17', '2022-08-22 15:34:43', '2022-08-22 15:34:43'),
(56, 1, '2023-04-24', '2022-08-22 16:26:33', '2022-08-22 16:26:33'),
(64, 22, '2023-04-03', '2023-02-07 16:18:00', '2023-02-07 16:18:00'),
(65, 22, '2023-04-10', '2023-02-07 16:18:29', '2023-02-07 16:18:29'),
(69, 2, '2023-04-03', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(70, 22, '2023-04-17', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(72, 2, '2023-04-17', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(73, 22, '2023-04-24', '2023-03-31 09:01:50', '2023-03-31 09:01:50'),
(78, 2, '2023-04-24', '2023-03-31 09:01:50', '2023-03-31 09:01:50');

reservations SQL:

SET NAMES 'utf8mb4';

INSERT INTO `bike-data`.reservations(id, cycle_id, ride_id, admin_id, first_name, ride_date, created_at, updated_at) VALUES
(2, 10, 1, 2, 'Test 2', '2023-04-03', '2022-07-27 15:39:54', '2023-04-18 15:07:00'),
(5, 11, 2, 2, 'Mickey', '2023-04-03', '2022-07-30 19:41:50', '2023-04-18 15:07:10'),
(11, 11, 2, 1, 'aa', '2023-04-10', '2022-08-13 15:59:37', '2023-04-19 13:38:54'),
(30, 8, 2, 22, 'GDPR', '2023-04-17', '2022-08-14 09:45:55', '2023-04-18 15:09:22'),
(31, 9, 1, NULL, 'GDPR-2', '2023-04-10', '2022-08-14 09:49:34', '2023-04-18 15:09:50'),
(32, 9, 2, 2, 'GDPR', '2023-04-17', '2022-08-14 09:58:10', '2023-04-18 15:10:29'),
(33, 10, 1, 28, 'GDPR', '2023-04-03', '2022-08-14 10:18:17', '2023-04-19 14:40:54'),
(34, 8, 2, 28, 'Peter', '2023-04-03', '2022-08-15 16:15:28', '2023-04-19 14:40:48'),
(35, 8, 2, 22, 'Peter', '2023-04-24', '2022-08-15 16:27:56', '2023-04-18 15:19:02'),
(36, 2, 1, NULL, 'Peter', '2023-04-17', '2022-08-22 20:19:27', '2023-04-18 15:19:34'),
(37, 3, 1, 1, 'Test', '2023-04-10', '2022-08-23 09:25:57', '2023-04-19 14:16:19'),
(39, 8, 1, NULL, 'Test', '2023-04-24', '2022-08-23 11:07:15', '2023-04-18 15:21:02');

With regard to expected results, take for example…

  1. admin_id 1 , this admin_id has 2 reservations for both possible ride_id on 10/04/2023, therefore as a result, no rows should be returned by the query as they are fully utilized.
  2. admin_id 22 , this admin_id has 1 reservation for ride_id 2 on 17/04/2023, therefore as ride_id 1 is still free for them on that date, crew_availability should still return a row (but doesn’t)
  3. any admin_id which has both ride_ids free on any one day would have an expected return of a single row from crew_availability.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Further work has led to a significant modification to the code, with the idea of using SELECT COUNT removed, I have instead joined the reservations table on itself. This query after a lot of testing appears to fully satisfy all 3 required outcomes...

    SELECT ca.id, ca.admin_id, ca.usable_date
FROM crew_availability ca
LEFT JOIN reservations r ON ca.admin_id = r.admin_id AND ca.usable_date = r.ride_date
WHERE r.ride_id IS NULL AND ca.usable_date IN ('2023-04-03', '2023-04-10', '2023-04-17', '2023-04-24')
UNION
(
SELECT r.id, r.admin_id, r.ride_date FROM
reservations r WHERE r.admin_id IS NOT NULL
EXCEPT
SELECT r.id, r.admin_id, r.ride_date FROM
reservations r
INNER JOIN
reservations r1
ON r.admin_id = r1.admin_id AND r.ride_date = r1.ride_date
WHERE r.id <> r1.id
);

  2. 0

    Here is your possible answer. Check the query. It should give you the result

    SELECT ca.id, ca.admin_id, ca.usable_date
FROM crew_availability ca
LEFT JOIN reservations r ON ca.admin_id = r.admin_id AND ca.usable_date = r.ride_date
WHERE r.ride_id IS NULL AND ca.usable_date = '2023-04-19'
AND (
  SELECT COUNT(*) FROM reservations r2 WHERE r2.admin_id = ca.admin_id AND r2.ride_date = ca.usable_date
) < 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search