upit na 4 tabele

Dobar dan.
Trebao bih pomoc oko rijesavanja jednog problema upita na 4 tabele.
Znam osnove i rijesiti jednostavnije primjere no kod zahtijevnijih zadataka stanem.

4 table:

tblCalendar
----------------------------------
date
Id city-department-office

tblEmployees
----------------
Id employees

tblSchedule
--------------
Id employees
Id department
date

City-department-office
--------------------------
Id city-department-office
Id department
Id office
Start time

Radnici se rasporede u grad po uredima i kancelarijama.

primjer tjednog razmjestaja radnika

schedule
Id-department 1.6. 2.6. 3.6. 4.6. 5.6. 6.6. 7.6.
10 1001 1002 1003 1004 1005 1006 1007
310 1002 1003 1004 1005 1006 1007 1008
610 1003 1004 1005 1006 1007 1008 1009
20 1004 1005 1006 1007 1008 1009 closed
320 1005 1006 1007 1008 1009 1001 closed
620 1006 1007 1008 1009 1001 closed closed
30 1007 1008 1009 1001 1002 closed closed
330 1008 1009 1001 1002 1003 closed closed
630 1009 1001 1002 1003 1004 closed closed

radnik 1001 1.6. dobio je odjel 10, sada pogledamo u kalendar za 1.6.

calendar
date Id city-department-office

1.6. 0106
2.6. 0106
3.6. 0106
4.6. 0106
5.6. 0106
6.6. 0606
7.6. 0706

za 1.6. u kalendaru kaze da za 1.6. pogledamo id 0106 u tabli City-department-office a tamo kaze da
radnik 1001 koji je dobio ured 10 radi u kancelariji 1 od 5:00.

kako naci za radnika gdje radi:

SELECT r.date, e.id_employees, e.name, s.id_department, s.id_office, s.startTime
FROM tbl_schedule as r
LEFT OUTER JOIN tbl_calendar AS k ON r.date = k.date
LEFT OUTER JOIN tbl_citydepartmentoffice AS s ON (k.id_cityDepartmentOffice = s.Id_cityDeparmentOffice AND r.id_department = s.id_department)
LEFT OUTER JOIN tbl_employees AS e ON r.id_employees=e.id_employees
WHERE r.id_employees = '1001' AND r.date >= DATE('2020-06-01')
ORDER BY r.date ASC
LIMIT 0 , 10

ispisace nam:

date id_employees name id_department id_office startTime
2020-06-01 1001 đon 10 1 05:00:00
2020-06-02 1001 đon 630 3 18:00:00
2020-06-03 1001 đon 330 3 12:00:00
2020-06-04 1001 đon 30 3 06:00:00
2020-06-05 1001 đon 620 2 17:30:00
2020-06-06 1001 đon 320 2 15:00:00
2020-06-07 1001 đon NULL NULL NULL

ono sto bih ja htio je upit koji bi mi pronasao tko je sve radio u istoj kancelariji u kojoj je radio i radnik 1001 isti dan kada i 1001

i tako za sve dane u tjednu. Uspio sam da napisem upit za jedan dan ali mi neide za vise dana.

evo upit koji pronalazi sve radnike koji su radili u kancelariji 1 na dan 1.6. kao i radnik 1001

SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime
FROM tbl_schedule as r1
INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
INNER JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
WHERE r1.date = DATE('2020-06-01')
AND s1.id_office =(SELECT s2.id_office
FROM tbl_schedule as r2
INNER JOIN tbl_calendar AS k2 ON r2.date = k2.date
INNER JOIN tbl_citydepartmentoffice AS s2 ON (k2.id_cityDepartmentOffice = s2.Id_cityDeparmentOffice AND r2.id_department = s2.id_department)
INNER JOIN tbl_employees AS e2 ON r2.id_employees=e2.id_employees
WHERE r2.id_employees='1001' AND r2.date = DATE('2020-06-01'))


rezultat:

date id_employees id_department id_office startTime
2020-06-01 1001 10 1 05:00:00
2020-06-01 1002 310 1 11:00:00
2020-06-01 1003 610 1 17:00:00


evo i data sa bazom:

-- phpMyAdmin SQL Dump
-- version 4.5.4.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 03, 2020 at 12:14 PM
-- Server version: 5.7.11
-- PHP Version: 5.6.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `testbaza`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_calendar`
--

CREATE TABLE `tbl_calendar` (
`date` date NOT NULL,
`id_cityDepartmentOffice` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_calendar`
--

INSERT INTO `tbl_calendar` (`date`, `id_cityDepartmentOffice`) VALUES
('2020-06-01', 106),
('2020-06-02', 106),
('2020-06-03', 106),
('2020-06-04', 106),
('2020-06-05', 106),
('2020-06-06', 606),
('2020-06-07', 706);

-- --------------------------------------------------------

--
-- Table structure for table `tbl_citydepartmentoffice`
--

CREATE TABLE `tbl_citydepartmentoffice` (
`Id_cityDeparmentOffice` int(11) NOT NULL,
`id_department` int(11) NOT NULL,
`id_office` int(11) NOT NULL,
`startTime` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_citydepartmentoffice`
--

INSERT INTO `tbl_citydepartmentoffice` (`Id_cityDeparmentOffice`, `id_department`, `id_office`, `startTime`) VALUES
(106, 10, 1, '05:00:00'),
(106, 20, 2, '05:30:00'),
(106, 30, 3, '06:00:00'),
(106, 310, 1, '11:00:00'),
(106, 320, 2, '11:30:00'),
(106, 330, 3, '12:00:00'),
(106, 610, 1, '17:00:00'),
(106, 620, 2, '17:30:00'),
(106, 630, 3, '18:00:00'),
(606, 10, 1, '05:00:00'),
(606, 20, 2, '09:00:00'),
(606, 310, 1, '11:00:00'),
(606, 320, 2, '15:00:00'),
(606, 610, 1, '17:00:00'),
(706, 10, 1, '06:00:00'),
(706, 310, 1, '12:00:00'),
(706, 610, 1, '18:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_employees`
--

CREATE TABLE `tbl_employees` (
`id_employees` int(11) NOT NULL,
`name` text NOT NULL,
`adress` text NOT NULL,
`phone` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_employees`
--

INSERT INTO `tbl_employees` (`id_employees`, `name`, `adress`, `phone`) VALUES
(1001, 'đon', '', ''),
(1002, 'sidnej', '', ''),
(1003, 'Mercel', '', ''),
(1004, 'Baterflaj', '', ''),
(1005, 'Lajon', '', ''),
(1006, 'Pupitron', '', ''),
(1007, 'Silvestar', '', ''),
(1008, 'Valentajn', '', ''),
(1009, 'Mekgres', '', ''),
(1010, 'Romanijan', '', '');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_schedule`
--

CREATE TABLE `tbl_schedule` (
`id_employees` int(11) NOT NULL,
`id_department` text NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_schedule`
--

INSERT INTO `tbl_schedule` (`id_employees`, `id_department`, `date`) VALUES
(1001, '10', '2020-06-01'),
(1001, '630', '2020-06-02'),
(1001, '330', '2020-06-03'),
(1001, '30', '2020-06-04'),
(1001, '620', '2020-06-05'),
(1001, '320', '2020-06-06'),
(1001, 'FD', '2020-06-07'),
(1002, '310', '2020-06-01'),
(1002, '10', '2020-06-02'),
(1002, '630', '2020-06-03'),
(1002, '330', '2020-06-04'),
(1002, '30', '2020-06-05'),
(1002, 'FD', '2020-06-06'),
(1002, 'FD', '2020-06-07'),
(1003, '610', '2020-06-01'),
(1003, '310', '2020-06-02'),
(1003, '10', '2020-06-03'),
(1003, '630', '2020-06-04'),
(1003, '330', '2020-06-05'),
(1003, 'FD', '2020-06-06'),
(1003, 'FD', '2020-06-07'),
(1004, '20', '2020-06-01'),
(1004, '610', '2020-06-02'),
(1004, '310', '2020-06-03'),
(1004, '10', '2020-06-04'),
(1004, '630', '2020-06-05'),
(1004, 'FD', '2020-06-06'),
(1004, 'FD', '2020-06-07'),
(1005, '320', '2020-06-01'),
(1005, '20', '2020-06-02'),
(1005, '610', '2020-06-03'),
(1005, '310', '2020-06-04'),
(1005, '10', '2020-06-05'),
(1005, 'FD', '2020-06-06'),
(1005, 'FD', '2020-06-07'),
(1006, '620', '2020-06-01'),
(1006, '320', '2020-06-02'),
(1006, '20', '2020-06-03'),
(1006, '610', '2020-06-04'),
(1006, '310', '2020-06-05'),
(1006, '10', '2020-06-06'),
(1006, 'FD', '2020-06-07'),
(1007, '30', '2020-06-01'),
(1007, '620', '2020-06-02'),
(1007, '320', '2020-06-03'),
(1007, '20', '2020-06-04'),
(1007, '610', '2020-06-05'),
(1007, '310', '2020-06-06'),
(1007, '10', '2020-06-07'),
(1008, '330', '2020-06-01'),
(1008, '30', '2020-06-02'),
(1008, '610', '2020-06-03'),
(1008, '320', '2020-06-04'),
(1008, '20', '2020-06-05'),
(1008, '610', '2020-06-06'),
(1008, '310', '2020-06-07'),
(1009, '630', '2020-06-01'),
(1009, '330', '2020-06-02'),
(1009, '30', '2020-06-03'),
(1009, '620', '2020-06-04'),
(1009, '320', '2020-06-05'),
(1009, '20', '2020-06-06'),
(1009, '610', '2020-06-07');


Mozda je prevelik post ali nisam mogao manje jer je puno za objasniti. Mislim da onima koji znaju je dovoljno i ono na pocetku ali za svaki slucaj stavio sam.

---