Gjoreski |
09.06.2020 16:39 |
Predmet:Re: SQL upiti
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) ;
|
zxz |
09.06.2020 19:54 |
Predmet:Re: SQL upiti
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- |
dex |
09.06.2020 21:11 |
Predmet:Re: SQL upiti
Napravi prvo View V1
PreuzmiIzvorni kôd (SQL):CREATE VIEW V1 AS
SELECT tblEmployees.Name, tblSchedule.DATE, tblCityDepartmentOffice.IDOffice,
tblCityDepartmentOffice.IDDepartment, tblCityDepartmentOffice.IDCityDepartmentOffice,
tblCityDepartmentOffice.StartTime
FROM (tblEmployees INNER JOIN tblSchedule
ON tblEmployees.IDEmployees = tblSchedule.IDEmployees)
INNER JOIN tblCityDepartmentOffice
ON tblSchedule.IDDepartment = tblCityDepartmentOffice.IDDepartment;
Pa onda
PreuzmiIzvorni kôd (SQL):SELECT V1.Name, V1.DATE, V1.IDCityDepartmentOffice, V1.StartTime FROM V1
INNER JOIN V1 AS T2
ON V1.DATE = T2.DATE
AND V1.Office = T2.IDOffice AND V1.IDDepartment = V1.IDDepartment
WHERE V1.name <> 'Vasko' AND T2.Name = 'Vasko'
|
Avko |
10.06.2020 09:37 |
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 | |
Avko |
10.06.2020 09:38 |
Predmet:Re: SQL upiti
Citiraj dex:Napravi prvo View V1
PreuzmiIzvorni kôd (SQL):CREATE VIEW V1 AS
SELECT tblEmployees.Name, tblSchedule.DATE, tblCityDepartmentOffice.IDOffice,
tblCityDepartmentOffice.IDDepartment, tblCityDepartmentOffice.IDCityDepartmentOffice,
tblCityDepartmentOffice.StartTime
FROM (tblEmployees INNER JOIN tblSchedule
ON tblEmployees.IDEmployees = tblSchedule.IDEmployees)
INNER JOIN tblCityDepartmentOffice
ON tblSchedule.IDDepartment = tblCityDepartmentOffice.IDDepartment;
Pa onda
PreuzmiIzvorni kôd (SQL):SELECT V1.Name, V1.DATE, V1.IDCityDepartmentOffice, V1.StartTime FROM V1
INNER JOIN V1 AS T2
ON V1.DATE = T2.DATE
AND V1.Office = T2.IDOffice AND V1.IDDepartment = V1.IDDepartment
WHERE V1.name <> 'Vasko' AND T2.Name = 'Vasko'
Na pocetku sam se i ja sam upustio u vies no problemi su nastali kad sam htio obrisati view sa delete if exist, ili isprazniti tu tablu. To sam rijesio preko admin panela (DROP). Onda sam pomislio koliko bi tek imao problema u php-u tako da sam odustao.
ovo je rezultat tvojeg upita:
name | date Ascending 1 | Id_cityDeparmentOffice | startTime | Luka | 2020-06-01 | 106 | 11:00:00 | Luka | 2020-06-01 | 106 | 11:00:00 | Luka | 2020-06-01 | 106 | 11:00:00 | Luka | 2020-06-01 | 606 | 11:00:00 | Luka | 2020-06-01 | 606 | 11:00:00 | Luka | 2020-06-01 | 606 | 11:00:00 | Luka | 2020-06-01 | 706 | 12:00:00 | Luka | 2020-06-01 | 706 | 12:00:00 | Luka | 2020-06-01 | 706 | 12:00:00 | Marko | 2020-06-01 | 106 | 17:00:00 | Marko | 2020-06-01 | 106 | 17:00:00 | Marko | 2020-06-01 | 106 | 17:00:00 | Marko | 2020-06-01 | 606 | 17:00:00 | Marko | 2020-06-01 | 606 | 17:00:00 | Marko | 2020-06-01 | 606 | 17:00:00 | Marko | 2020-06-01 | 706 | 18:00:00 | Marko | 2020-06-01 | 706 | 18:00:00 | Marko | 2020-06-01 | 706 | 18:00:00 | Ahmed | 2020-06-02 | 106 | 06:00:00 | Aleksandar | 2020-06-02 | 106 | 12:00:00 | Luka | 2020-06-03 | 106 | 18:00:00 | Aleksandar | 2020-06-03 | 106 | 06:00:00 | Luka | 2020-06-04 | 106 | 12:00:00 | Marko | 2020-06-04 | 106 | 18:00:00 | Ahmed | 2020-06-05 | 106 | 05:30:00 | Ahmed | 2020-06-05 | 606 | 09:00:00 | Aleksandar | 2020-06-05 | 106 | 11:30:00 | Aleksandar | 2020-06-05 | 606 | 15:00:00 | Aleksandar | 2020-06-06 | 106 | 05:30:00 | Aleksandar | 2020-06-06 | 106 | 05:30:00 | Aleksandar | 2020-06-06 | 606 | 09:00:00 | Aleksandar | 2020-06-06 | 606 | 09:00:00 |
znaci ovo treba ostati nakon upita:
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 | 2020-06-07 | 1001 | FD | NULL NULL | | |
Avko |
10.06.2020 09:39 |
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. |
Gjoreski |
10.06.2020 11:13 |
Predmet:Re: SQL upiti
Ne moras if then dodaj union upit samo za oni sa FD. |
zxz |
10.06.2020 11:21 |
Predmet:Re: SQL upiti
Citat:Treba napisati upit: tko je sve radio sa Vaskom u kancelariji? Ovi rezultati sto ti trazis ne zadovoljavaju ovoaj zadatak.
Ovi ljudim nisu radili sa vaskom u kancelarji a jedino vasko sam sa sobom jest rdi.
Ako se trazi ko je raio sa Vaskom onda tu netreba biti Vasko,
Drugo ovi ljudm sau radili u kancelarijama koje imaju isti broj kao i vaskova kancelarija a ne u istim
kancelarijama.
date | id_employees | name | Id_cityDeparmentOffice | id_department | id_office |
---|
01/06/2020 | 1001 | Vasko | 106 | 10 | 1 | 01/06/2020 | 1001 | Vasko | 606 | 10 | 1 | 01/06/2020 | 1001 | Vasko | 706 | 10 | 1 | 01/06/2020 | 1002 | Luka | 106 | 310 | 1 | 01/06/2020 | 1002 | Luka | 606 | 310 | 1 | 01/06/2020 | 1002 | Luka | 706 | 310 | 1 | 01/06/2020 | 1003 | Marko | 106 | 610 | 1 | 01/06/2020 | 1003 | Marko | 606 | 610 | 1 | 01/06/2020 | 1003 | Marko | 706 | 610 | 1 | 01/06/2020 | 1004 | Ivan | 106 | 20 | 2 | 01/06/2020 | 1004 | Ivan | 606 | 20 | 2 | 01/06/2020 | 1005 | Marta | 106 | 320 | 2 | 01/06/2020 | 1005 | Marta | 606 | 320 | 2 | 01/06/2020 | 1006 | Smiljan | 106 | 620 | 2 | 01/06/2020 | 1007 | Mitar | 106 | 30 | 3 | 01/06/2020 | 1008 | Ahmed | 106 | 330 | 3 | 01/06/2020 | 1009 | Aleksandar | 106 | 630 | 3 |
Iz prilozenog se vidi da je na dan 2020-06-01 Vasko radio u Id_cityDeparmentOffice 106,id_department 10 u id_office 1.
Na taj dan u tom gradu u toj zgradi nije vise niko radi.
Cak u istj zgradi sa njim nije vise niko radio. |
Avko |
10.06.2020 16:23 |
Predmet:Re: SQL upiti
kancelarija ima id, na to sam i mislio. Tko je sve radio u id kancelarija kao i vsasko |
Gjoreski |
10.06.2020 20:04 |
Predmet:Re: SQL upiti
jel datum uslov ili nije |