[ hrg @ 03.06.2020. 14:10 ] @
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. |