Centar za edukaciju-BiH

PreuzmiIzvorni kôd (SQL):

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. LEFT 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. INNER JOIN (
7.     SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
8.     FROM tbl_schedule AS ra1
9.     INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
10.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
11.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
12.     WHERE ea1.id_employees=1001) tUs
13. ON r1.DATE=tUs.DATE AND s1.id_office=tUs.id_office
14.  
15. UNION ALL
16.  
17. SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime
18. FROM tbl_schedule AS r1
19. INNER JOIN tbl_calendar AS k1 ON r1.DATE = k1.DATE
20. LEFT JOIN tbl_citydepartmentoffice AS s1 ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
21. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
22. INNER JOIN (
23.     SELECT ra1.DATE, ea1.id_employees, ra1.id_department, sa1.id_office, sa1.startTime
24.     FROM tbl_schedule AS ra1
25.     INNER JOIN tbl_calendar AS ka1 ON ra1.DATE = ka1.DATE
26.     LEFT JOIN tbl_citydepartmentoffice AS sa1 ON (ka1.id_cityDepartmentOffice = sa1.Id_cityDeparmentOffice AND ra1.id_department = sa1.id_department)
27.     INNER JOIN tbl_employees AS ea1 ON ra1.id_employees=ea1.id_employees
28.     WHERE ea1.id_employees=1001) tUs
29. ON r1.DATE=tUs.DATE AND e1.id_employees=tUs.id_employees
30. WHERE ISNULL(s1.id_office) ;

Ti trazis ovo rjesenje ali ovo rjesenje nije tacno po meni.

PreuzmiIzvorni kôd (Text):

1. SELECT  rv.Date
2. ,rv.id_employees
3. ,r.name
4.  
5. ,rv.id_department
6. ,o.id_office
7. ,o.startTime
8. FROM tbl_schedule as rv
9. INNER JOIN tbl_employees  as r ON rv.id_employees=r.id_employees
10. INNER JOIN tbl_calendar as k   ON k.date=rv.date
11. INNER JOIN tbl_citydepartmentoffice as o   ON o.Id_cityDeparmentOffice=k.id_cityDepartmentOffice
12. AND rv.id_department=o.id_department
13. WHERE  CONCAT(o.id_office,rv.Date)
14. IN (SELECT CONCAT(o.id_office,rv.Date) as kljuc
15. FROM tbl_schedule as rv
16. INNER JOIN tbl_citydepartmentoffice as o   ON rv.id_department=o.id_department
17. WHERE rv.id_employees=1001
18. )
19. AND
20. 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-

Napravi prvo View V1

PreuzmiIzvorni kôd (SQL):

1. CREATE VIEW V1 AS
2. SELECT tblEmployees.Name, tblSchedule.DATE, tblCityDepartmentOffice.IDOffice,
3.  tblCityDepartmentOffice.IDDepartment, tblCityDepartmentOffice.IDCityDepartmentOffice,
4.  tblCityDepartmentOffice.StartTime
5. FROM (tblEmployees INNER JOIN tblSchedule
6. ON tblEmployees.IDEmployees = tblSchedule.IDEmployees)
7. INNER JOIN tblCityDepartmentOffice
8. ON tblSchedule.IDDepartment = tblCityDepartmentOffice.IDDepartment;

Pa onda

PreuzmiIzvorni kôd (SQL):

1. SELECT V1.Name, V1.DATE, V1.IDCityDepartmentOffice, V1.StartTime FROM V1
2. INNER JOIN V1 AS T2
3. ON V1.DATE = T2.DATE
4. AND  V1.Office = T2.IDOffice AND V1.IDDepartment = V1.IDDepartment
5. WHERE V1.name <> 'Vasko' AND T2.Name = 'Vasko'

Treba napisati upit: tko je sve radio sa Vaskom u kancelariji?