Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Select appointment_dates where all dates are unconfirmed for a specific appointment
DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
id integer,
title varchar(255),
created_at date
);
DROP TABLE IF EXISTS appointment_dates;
CREATE TABLE appointment_dates (
id integer,
appointment_id integer,
confirmed boolean,
created_at date
);
INSERT INTO appointments VALUES (1, 'one', '2018-05-21');
INSERT INTO appointment_dates VALUES (1, 1, false, '2018-05-21');
INSERT INTO appointment_dates VALUES (2, 1, true, '2018-05-21');
INSERT INTO appointment_dates VALUES (3, 1, false, '2018-05-21');
INSERT INTO appointment_dates VALUES (4, 1, false, '2018-05-21');
INSERT INTO appointments VALUES (2, 'two', '2018-05-21');
INSERT INTO appointment_dates VALUES (5, 2, false, '2018-05-21');
INSERT INTO appointment_dates VALUES (6, 2, false, '2018-05-21');
INSERT INTO appointments VALUES (3, 'three', '2018-05-22');
INSERT INTO appointment_dates VALUES (7, 3, false, '2018-05-22');
INSERT INTO appointment_dates VALUES (8, 3, false, '2018-05-22');
INSERT INTO appointments VALUES (4, 'four', '2018-05-21');
INSERT INTO appointment_dates VALUES (9, 4, false, '2018-05-21');
INSERT INTO appointment_dates VALUES (10, 4, false, '2018-05-21');
INSERT INTO appointments VALUES (5, 'five', '2018-05-21');
INSERT INTO appointment_dates VALUES (11, 5, false, '2018-05-21');
INSERT INTO appointment_dates VALUES (12, 5, false, '2018-05-21');
INSERT INTO appointments VALUES (6, 'six', '2018-05-21');
INSERT INTO appointments VALUES (7, 'seven', '2018-05-21');
SELECT appointment_id
FROM appointment_dates ad
WHERE created_at = '2018-05-21'
GROUP BY appointment_id
HAVING SUM(CASE ad.confirmed WHEN TRUE THEN 1 ELSE 0 END) = 0
@phallstrom

This comment has been minimized.

Copy link

phallstrom commented May 22, 2018

CREATE TABLE appointments (
  id        integer,
  title     varchar(255),
  created_at date
);

DROP TABLE IF EXISTS appointment_dates;
CREATE TABLE appointment_dates (
  id        integer,
  appointment_id integer,
  appointment_date_and_time timestamp,
  confirmed boolean
);

INSERT INTO appointments VALUES (1, 'one', '2018-05-21');
INSERT INTO appointment_dates VALUES (1, 1, '2018-05-21 08:00:00', false);
INSERT INTO appointment_dates VALUES (2, 1, '2018-05-21 09:00:00', true);

INSERT INTO appointments VALUES (2, 'two', '2018-05-21');
INSERT INTO appointment_dates VALUES (3, 2, '2018-05-21 08:00:00', false);
INSERT INTO appointment_dates VALUES (4, 2, '2018-05-21 09:00:00', false);

INSERT INTO appointments VALUES (3, 'three', '2018-05-22');
INSERT INTO appointment_dates VALUES (5, 3, '2018-05-22 08:00:00', false);
INSERT INTO appointment_dates VALUES (6, 3, '2018-05-22 09:00:00', false);

INSERT INTO appointments VALUES (4, 'four', '2018-05-21');

SELECT *
FROM appointments
WHERE id IN (
  SELECT appointment_id
  FROM appointment_dates ad
  WHERE appointment_date_and_time BETWEEN '2018-05-21' AND '2018-05-22'
  GROUP BY appointment_id
  HAVING SUM(CASE ad.confirmed WHEN TRUE THEN 1 ELSE 0 END) = 0
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.