Vagtskema

CREATE TABLE `tbl_skema` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `vagt_type_id` int(11) NOT NULL,
  `dato` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tbl_skema` (`id`, `user_id`, `vagt_type_id`, `dato`) VALUES
(1, 3, 2, '2018-11-19'),
(2, 4, 1, '2018-11-19'),
(3, 1, 1, '2018-11-19'),
(4, 1, 3, '2018-11-18');

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL,
  `firstname` varchar(255) COLLATE utf8_danish_ci NOT NULL,
  `lastname` varchar(255) COLLATE utf8_danish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;


INSERT INTO `tbl_users` (`id`, `firstname`, `lastname`) VALUES
(1, 'rip', 'andemad'),
(3, 'rap', 'and'),
(4, 'rup', 'and'),
(5, 'mickey', 'mouse'),
(7, 'bimmer', 'vildmand'),
(8, 'jule', 'mand');

CREATE TABLE `tbl_vagt_typer` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_danish_ci NOT NULL,
  `tid_start` time NOT NULL,
  `tid_slut` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tbl_vagt_typer` (`id`, `name`, `tid_start`, `tid_slut`) VALUES
(1, 'dagvagt', '07:00:00', '15:00:00'),
(2, 'aftenvagt', '15:00:00', '23:00:00'),
(3, 'kontor', '07:00:00', '15:00:00');


ALTER TABLE `tbl_skema`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `tbl_users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_lastname` (`lastname`);


ALTER TABLE `tbl_vagt_typer`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `tbl_skema`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE `tbl_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;


ALTER TABLE `tbl_vagt_typer`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

SELECT * FROM `tbl_skema` JOIN tbl_users on tbl_skema.user_id = tbl_users.id JOIN tbl_vagt_typer on tbl_skema.vagt_type_id = tbl_vagt_typer.id

SELECT * FROM `tbl_skema` JOIN tbl_users on tbl_skema.user_id = tbl_users.id JOIN tbl_vagt_typer on tbl_skema.vagt_type_id = tbl_vagt_typer.id WHERE tbl_skema.dato ='2018-11-19'