iCentar » Racunari i oprema » Programirannje i baze podataka » MySQL

Gjoreski	09.06.2020 15: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) ;

Gjoreski

09.06.2020 15: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 18: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-

zxz

09.06.2020 18: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 20: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'

dex

09.06.2020 20: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 08: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 08: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 08: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 10:13
Predmet:Re: SQL upiti Ne moras if then dodaj union upit samo za oni sa FD.

Gjoreski

10.06.2020 10:13

Predmet:Re: SQL upiti

Ne moras if then dodaj union upit samo za oni sa FD.

zxz

10.06.2020 10: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.