Predmet:Re: SQL upiti
sada sa 4 tbl
tblCalendar
---------------
Date
IdCityDepartmentOffice
tblEmployees
-----------------
IdEmployees
Name
Adress
Phone
Email
tblSchedule
---------------
IdEmployees
IdDepartment
Date
tblCityDepartmentOffice
-------------------------------
IdCityDepartmentOffice
IdDepartment
IdOffice
StartTime
DATA
schedule-Å¡ema=>ovo nije tabla samo da se vidi kako funkcionira |
IdDepartment |
10 |
310 |
610 |
20 |
320 |
620 |
30 |
330 |
630 |
tblSchedule |
Id employees |
1001 |
1001 |
1001 |
1001 |
1001 |
1001 |
1001 |
1002 |
1002 |
1002 |
1002 |
1002 |
1002 |
1002 |
1003 |
1003 |
1003 |
1003 |
1003 |
1003 |
1003 |
1004 |
1004 |
1004 |
1004 |
1004 |
1004 |
1004 |
1005 |
1005 |
1005 |
1005 |
1005 |
1005 |
1005 |
1006 |
1006 |
1006 |
1006 |
1006 |
1006 |
1006 |
1007 |
1007 |
1007 |
1007 |
1007 |
1007 |
1007 |
1008 |
1008 |
1008 |
1008 |
1008 |
1008 |
1008 |
1009 |
1009 |
1009 |
1009 |
1009 |
1009 |
1009 |
tblCalendar |
date |
1.6. |
2.6. |
3.6. |
4.6. |
5.6. |
6.6. |
7.6. |
Employees |
Id employees |
1001 |
1002 |
1003 |
1004 |
1005 |
1006 |
1007 |
1008 |
1009 |
tblCityDepartmentOffice |
IdCityDepartmentOffice |
0106 |
0106 |
0106 |
0106 |
0106 |
0106 |
0106 |
0106 |
0106 |
0606 |
0606 |
0606 |
0606 |
0606 |
0706 |
0706 |
0706 |
U kojoj kancelariji (office) je radio Vasko?
PreuzmiIzvorni kôd (Text):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 | |
Pitanje:
Treba napisati upit: tko je sve radio sa Vaskom u kancelariji?
rezultat bi trebao pokazati:
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 | |
2020-06-02 | 1003 | 30 | 3 | 06:00:00 | |
2020-06-02 | 1008 | 330 | 3 | 12:00:00 | |
2020-06-02 | 1009 | 630 | 3 | 18:00:00 | |
2020-06-03 | 1009 | 30 | 3 | 06:00:00 | |
2020-06-03 | 1001 | 330 | 3 | 12:00:00 | |
2020-06-03 | 1002 | 630 | 3 | 18:00:00 | |
2020-06-04 | 1001 | 30 | 3 | 06:00:00 | |
2020-06-04 | 1002 | 330 | 3 | 12:00:00 | |
2020-06-04 | 1003 | 630 | 3 | 18:00:00 | |
2020-06-05 | 1008 | 20 | 2 | 05:30:00 | |
2020-06-05 | 1009 | 320 | 2 | 11:30:00 | |
2020-06-05 | 1001 | 620 | 2 | 17:30:00 | |
2020-06-06 | 1009 | 20 | 2 | 09:00:00 | |
2020-06-06 | 1001 | 320 | 2 | 15:00:00 | |
2020-06-07 | 1001 | FD | null | null | |
zivot je moja domovina.
Ovaj post je ureden
1
puta. Posljednja izmjena 05.06.2020 18:54 od strane Avko.