iCentar

05.06.2020 18:53

Avko

Administrator

Registrovan od:28.05.2014
Lokacija:zagreb

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.