1

I have a table Appointments which has start_date, end_date, cabinet_id, and many-to-many relationship with specialists via join table. Basicaly I need to get all free slots between appointments where cabinet_id is exact and multiple specialists ids are given. For free slots I have written query which finds free slots by cabinet_id but have no idea how to modify query to do selecting free times based on many-to-many querying.

Basicaly I need to find free slots where all specialists are free and cabinet_id is also free.

My current query for free slots:

SELECT
  a.cabinet_id, 
  a.end_time AS 'Unused From', 
  Min(b.start_time) AS 'Until'
FROM `Appointment` AS a  
JOIN `Appointment` AS b ON a.cabinet_id=b.cabinet_id AND a.end_time <= b.start_time
WHERE a.cabinet_id = 4
GROUP BY a.end_time
HAVING a.end_time < MIN(b.start_time) 
ORDER BY cabinet_id

And table structures:

CREATE TABLE `Appointment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cabinet_id` int(11) DEFAULT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_78A47793D351EC` (`cabinet_id`),
  KEY `IDX_78A477936B899279` (`patient_id`),
  CONSTRAINT `FK_78A477936B899279` FOREIGN KEY (`patient_id`) REFERENCES `Patient` (`id`),
  CONSTRAINT `FK_78A47793D351EC` FOREIGN KEY (`cabinet_id`) REFERENCES `Cabinet` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=338 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `appointment_specialist` (
  `appointment_id` int(11) NOT NULL,
  `specialist_id` int(11) NOT NULL,
  PRIMARY KEY (`appointment_id`,`specialist_id`),
  KEY `IDX_BB6E783EE5B533F9` (`appointment_id`),
  KEY `IDX_BB6E783E7B100C1A` (`specialist_id`),
  CONSTRAINT `FK_BB6E783E7B100C1A` FOREIGN KEY (`specialist_id`) REFERENCES `Specialist` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_BB6E783EE5B533F9` FOREIGN KEY (`appointment_id`) REFERENCES `Appointment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `Specialist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `profession` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `color` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8D7A4924A76ED395` (`user_id`),
  CONSTRAINT `FK_8D7A4924A76ED395` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Rob
  • 12,659
  • 4
  • 39
  • 56
Aurimas Niekis
  • 227
  • 1
  • 14
  • 1
    You'd need to simulate a full outer join by using two queries (one left & one right, two left with re-ordering of tables, or two right with re-ordering of tables. This way you get the first and last records for cabinet ID. Without them you'll miss your edges; and if a cabinet had no appointments, you'd miss it entirely. – xQbert Sep 04 '14 at 20:51
  • Could you help me write this because I have no idea how to write this... – Aurimas Niekis Sep 04 '14 at 21:20
  • I'll look at it later if someone else has not... Maybe someone has more time at the moment as I'm heading out. – xQbert Sep 04 '14 at 21:21
  • See this answer for information on full outer joins in MySQL: http://stackoverflow.com/a/4796911/2427560 – Turophile Sep 04 '14 at 21:33
  • Not really helps me to understand what xQbert ment... – Aurimas Niekis Sep 04 '14 at 22:45
  • It will help if you provide some sample data and desired output – cha Sep 04 '14 at 22:49

0 Answers0