Predmet:Re: SQL upiti
Citiraj Gjoreski:PreuzmiIzvorni kôd (SQL):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
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 s1.id_office=tUs.id_office
UNION ALL
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
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) ;
čudno mi je radio ovaj sql, nakon Å¡to sam ga pokrenuo on se izvrÅ¡io i dao mi mogučnost editiranja i promjena , a očekivao sam samo rezultat. Evo Å¡to sam dobio:
Full texts | date | id_employees | id_department | id_office | startTime |
Edit Edit | Copy Copy | Delete Delete | 2020-06-01 | 1001 | 10 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-02 | 1001 | 630 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-03 | 1001 | 330 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-04 | 1001 | 30 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-05 | 1001 | 620 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-06 | 1001 | 320 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-01 | 1002 | 310 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-03 | 1002 | 630 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-04 | 1002 | 330 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-01 | 1003 | 610 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-04 | 1003 | 630 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-02 | 1008 | 30 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-05 | 1008 | 20 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-02 | 1009 | 330 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-03 | 1009 | 30 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-05 | 1009 | 320 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-06 | 1009 | 20 |
Edit Edit | Copy Copy | Delete Delete | 2020-06-07 | 1001 | FD |
nažalost, nije točno.
Citiraj zxz:Ti trazis ovo rjesenje ali ovo rjesenje nije tacno po meni.
PreuzmiIzvorni kôd (Text):SELECT rv.Date
,rv.id_employees
,r.name
,rv.id_department
,o.id_office
,o.startTime
FROM tbl_schedule as rv
INNER JOIN tbl_employees as r ON rv.id_employees=r.id_employees
INNER JOIN tbl_calendar as k ON k.date=rv.date
INNER JOIN tbl_citydepartmentoffice as o ON o.Id_cityDeparmentOffice=k.id_cityDepartmentOffice
AND rv.id_department=o.id_department
WHERE CONCAT(o.id_office,rv.Date)
IN (SELECT CONCAT(o.id_office,rv.Date) as kljuc
FROM tbl_schedule as rv
INNER JOIN tbl_citydepartmentoffice as o ON rv.id_department=o.id_department
WHERE rv.id_employees=1001
)
AND
rv.id_employees<>1001
evo zasto nije tacno.
grad
---------
id grad
id kancelarija
id zgrada
kancelarija jeste ista ali nije u istom gradu i u istoj zgradi-
Dodao sam na kraj ORDER BY rv.date ASC.
Ovaj upit napravi dobro ali izostavi 1001
evo rezultat:
Date | id_employees | name | id_department | id_office | startTime |
2020-06-01 | 1002 | Luka | 310 | 1 | 11:00:00 |
2020-06-01 | 1003 | Marko | 610 | 1 | 17:00:00 |
2020-06-02 | 1008 | Ahmed | 30 | 3 | 06:00:00 |
2020-06-02 | 1009 | Aleksandar | 330 | 3 | 12:00:00 |
2020-06-03 | 1002 | Luka | 630 | 3 | 18:00:00 |
2020-06-03 | 1009 | Aleksandar | 30 | 3 | 06:00:00 |
2020-06-04 | 1002 | Luka | 330 | 3 | 12:00:00 |
2020-06-04 | 1003 | Marko | 630 | 3 | 18:00:00 |
2020-06-05 | 1008 | Ahmed | 20 | 2 | 05:30:00 |
2020-06-05 | 1009 | Aleksandar | 320 | 2 | 11:30:00 |
2020-06-06 | 1009 | Aleksandar | 20 | 2 | 09:00:00 |
zivot je moja domovina.