Prikazi cijelu temu 10.06.2020 09:39
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Lokacija:zagreb


Predmet:Re: SQL upiti
e sada ja sam nesto ispobavao i dosao do ovoga upita:

PreuzmiIzvorni kôd (Text):
  1. SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime
  2. FROM tbl_schedule as r1
  3. INNER JOIN tbl_calendar AS k1 ON r1.date = k1.date
  4. INNER JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  5. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  6. WHERE r1.date >= DATE('2020-06-01')
  7. AND s1.id_office =(
  8.         SELECT s2.id_office
  9.         FROM tbl_schedule as r2
  10.         INNER JOIN tbl_calendar AS k2 ON r2.date = k2.date
  11.         INNER JOIN tbl_citydepartmentoffice AS s2 ON (k2.id_cityDepartmentOffice = s2.Id_cityDeparmentOffice AND r2.id_department = s2.id_department)
  12.         INNER JOIN tbl_employees AS e2 ON r2.id_employees=e2.id_employees
  13.         WHERE r2.id_employees='1001' AND r2.date = r1.date)
  14. 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 1id_employeesid_departmentid_officestartTime
2020-06-01100110105:00:00
2020-06-011002310111:00:00
2020-06-011003610117:00:00
2020-06-02100830306:00:00
2020-06-021009330312:00:00
2020-06-021001630318:00:00
2020-06-03100930306:00:00
2020-06-031001330312:00:00
2020-06-031002630318:00:00
2020-06-04100130306:00:00
2020-06-041002330312:00:00
2020-06-041003630318:00:00
2020-06-05100820205:30:00
2020-06-051009320211:30:00
2020-06-051001620217:30:00
2020-06-06100920209:00:00
2020-06-061001320215: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.