Predmet:Re: SQL upiti
e sada ja sam nesto ispobavao i dosao do ovoga upita:
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 = r1.date)
ORDER BY r1.date ASC, r1.id_department ASC;
Taj upit mi da tocan rezultat uz to da mi ne ispiše za 7.6. FD
date Ascending 1 | 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 | 1008 | 30 | 3 | 06:00:00 |
2020-06-02 | 1009 | 330 | 3 | 12:00:00 |
2020-06-02 | 1001 | 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 |
razmisljao sam upotrijebiti IF THEN ELSE, recimo ako je u schedule id_department > 0 onda izvršiti gornji upit, a ako schedule.id_department nije broj NOT NUMERIC ili ISNUMERIC (neznam kako u mysql-u) onda samo ispiše za taj datum schedule.id_department
eto tako sam ja to zamislio.
zivot je moja domovina.