iCentar

12.06.2020 23:08

Avko

Administrator

Registrovan od:28.05.2014
Lokacija:zagreb

Predmet:Re: SQL upiti

1. sql

PreuzmiIzvorni kôd (Text):

SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
LEFT 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
INNER JOIN (
SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON CONCAT(s1.id_office,'_',r1.date) = CONCAT(tUs.id_office,'_',tUs.date)

rezultat:

date	id_employees	id_department	id_office	startTime	Uslov
2020-06-01	1001	10	1	05:00:00	10_1_2020-06-01
2020-06-01	1002	310	1	11:00:00	310_1_2020-06-01
2020-06-01	1003	610	1	17:00:00	610_1_2020-06-01
2020-06-04	1001	30	3	06:00:00	30_3_2020-06-04
2020-06-04	1002	330	3	12:00:00	330_3_2020-06-04
2020-06-04	1003	630	3	18:00:00	630_3_2020-06-04
2020-06-03	1001	330	3	12:00:00	330_3_2020-06-03
2020-06-03	1002	630	3	18:00:00	630_3_2020-06-03
2020-06-03	1009	30	3	06:00:00	30_3_2020-06-03
2020-06-05	1001	620	2	17:30:00	620_2_2020-06-05
2020-06-05	1008	20	2	05:30:00	20_2_2020-06-05
2020-06-05	1009	320	2	11:30:00	320_2_2020-06-05
2020-06-02	1001	630	3	18:00:00	630_3_2020-06-02
2020-06-02	1008	30	3	06:00:00	30_3_2020-06-02
2020-06-02	1009	330	3	12:00:00	330_3_2020-06-02
2020-06-06	1001	320	2	15:00:00	320_2_2020-06-06
2020-06-06	1009	20	2	09:00:00	20_2_2020-06-06

2.sql

PreuzmiIzvorni kôd (Text):

SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
FROM tbl_schedule AS r1
INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
LEFT 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
INNER JOIN (
SELECT ra1.date, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
FROM tbl_schedule AS ra1
INNER JOIN tbl_calendar AS ka1 ON ra1.date = ka1.date
LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
WHERE ea1.id_employees=1001) tUs
ON r1.date=tUs.date AND e1.id_employees=tUs.id_employees
WHERE ISNULL(s1.id_office)
ORDER BY date ASC, id_department ASC

date	id_employees	id_department	id_office	startTime	Uslov
2020-06-07	1001	FD	NULL NULL NULL

to je to, hvala Gjoreski. Rezultat je ono sto sam trazio.
Potrazit cu rijesenje za union all i prouciti.

hvala jos jednom

zivot je moja domovina.