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

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

Pretrazi
Tim
Registriraj se
Vazni alati
Razno. veze
- Oznaci forume kao procitane
- Smjernice
Aktivne teme ...
- u posljednja 24 sata
- od moje posljednje posjete
Vijesti (RSS, ...)
- RSS 2.0
- RSS 1.0
- OPML
- Klipfolio
- Atom 1.0
Prijavi se

Opcije teme

Stranice (6):1,2,3,4,5,6

Prikazi prvu neprocitanu poruku

#41

10.06.2020 20:34

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

Citiraj Gjoreski:

jel datum uslov ili nije

zapocne se od danasnjeg dana, a danasnji dan je recimo 1.6.
sutra ce biti 2.6 itd.

recimo da ce WHERE biti date>='$danasnjiDatum'

zivot je moja domovina.

#42

10.06.2020 23:05

Gjoreski

Administrator

Registrovan od:02.02.2009
Postovi:1,828

Predmet:Re: SQL upiti

Opet se nerazumemo
Pitajne glasi.
1. Ako je datum uslov od cega se sostoi on ? 1. Jedan datum
2. Moze biti vise datuma
2. id_Office je isto uslov
- E sad ako je jedan datum onda imamo uslov r1.date >= DATE('2020-06-01') sta je ispravno ali ces dobiti
podatke samo za tog datuma
-- Ako je ovako dobicemo Sve radnike koi su radili u kancalarijama gde je radio Vasko bez razlika dali
Vasko je bio na posao
- Ako su vise datuma po koj kriterium ih biramo
Dali su oni u nekakav raspon OD DO ili je uslov Vasko kad je radio
Ako je u rasponu OD DO bobijamo isti rezultat kako i predhodno samo za odredzeni period
Ako je uslov Vasko za dobijajne datuma onda je moje resejne od posta #31 ispravno
Znaci u resejne #31 imamo uslov ON r1.date=tUs.date AND s1.id_office=tUs.id_office

Evo sta jas dobijam

Slicice prilozenih slika:

Vasko.png
Tip datoteke: Informacije o tipu datoteke za:png

png
Preuzimanja:113
Velicina datoteke:41.13 KB
Velicina slike: 766 x 464 Pikseli

#43

10.06.2020 23:29

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

da to je tocno, ali kada pokrenem taj upit ja dobijem ovo:

i nemogu sortirati po datumu

zivot je moja domovina.

#44

11.06.2020 00:10

Gjoreski

Administrator

Registrovan od:02.02.2009
Postovi:1,828

Predmet:Re: SQL upiti

PreuzmiIzvorni kôd (SQL):

SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
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 CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
ORDER BY r1.DATE ASC, r1.id_department ASC;

#45

11.06.2020 00:39

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

@Gjoreski

date Ascending 1	id_employees	id_department	id_office	startTime	Uslov
2020-06-01	1001	10	1	05:00:00	10_1_2020-06-01
2020-06-01	1002	310	1	11:00:00	310_1_2020-06-01
2020-06-01	1003	610	1	17:00:00	610_1_2020-06-01
2020-06-02	1008	30	3	06:00:00	30_3_2020-06-02
2020-06-02	1009	330	3	12:00:00	330_3_2020-06-02
2020-06-02	1001	630	3	18:00:00	630_3_2020-06-02
2020-06-03	1009	30	3	06:00:00	30_3_2020-06-03
2020-06-03	1001	330	3	12:00:00	330_3_2020-06-03
2020-06-03	1002	630	3	18:00:00	630_3_2020-06-03
2020-06-04	1001	30	3	06:00:00	30_3_2020-06-04
2020-06-04	1002	330	3	12:00:00	330_3_2020-06-04
2020-06-04	1003	630	3	18:00:00	630_3_2020-06-04
2020-06-05	1008	20	2	05:30:00	20_2_2020-06-05
2020-06-05	1009	320	2	11:30:00	320_2_2020-06-05
2020-06-05	1001	620	2	17:30:00	620_2_2020-06-05
2020-06-06	1009	20	2	09:00:00	20_2_2020-06-06
2020-06-06	1001	320	2	15:00:00	320_2_2020-06-06

to je to ali fali joÅ¡ da napiÅ¡e i

2020-06-07

1001

zivot je moja domovina.

#46

11.06.2020 16:34

Gjoreski

Administrator

Registrovan od:02.02.2009
Postovi:1,828

Predmet:Re: SQL upiti

PreuzmiIzvorni kôd (SQL):

SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
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 CONCAT(s1.id_office,'_',r1.DATE) = CONCAT(tUs.id_office,'_',tUs.DATE)
UNION ALL
SELECT r1.DATE, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.DATE) AS Uslov
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)
ORDER BY DATE ASC, id_department ASC

#47

12.06.2020 00:44

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

1. nema 1001 za 1.6.
2. zasto mi u rezultatu daje EDIT, COPY, DELETE kolone? (mozda ovo LINK, greÅ¡ka u phpAdminu, koliko sam razumio)

zivot je moja domovina.

Ovaj post je ureden 1 puta. Posljednja izmjena 12.06.2020 00:48 od strane Avko.

#48

12.06.2020 10:01

Gjoreski

Administrator

Registrovan od:02.02.2009
Postovi:1,828

Predmet:Re: SQL upiti

Ja sam na sql a koi si potvrdio dodao jos jedan sql sa union za ono FD sta nije bilo . Smatram da je problem taj PHP myAdmin koj koristis,Potrazi uzmi neki alat za misajne koda u my sql i vidi sta ces dobiti.
Za pocetak pokreni oba dva sql a bez Union All I pogledaj rezultat.
Evo sta ja dobijam:

Slicice prilozenih slika:

1111.png
Tip datoteke: Informacije o tipu datoteke za:png

png
Preuzimanja:145
Velicina datoteke:51.23 KB
Velicina slike: 916 x 479 Pikseli

#49

12.06.2020 23:08

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

1. sql

PreuzmiIzvorni kôd (Text):

SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
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 CONCAT(s1.id_office,'_',r1.date) = CONCAT(tUs.id_office,'_',tUs.date)

rezultat:

date	id_employees	id_department	id_office	startTime	Uslov
2020-06-01	1001	10	1	05:00:00	10_1_2020-06-01
2020-06-01	1002	310	1	11:00:00	310_1_2020-06-01
2020-06-01	1003	610	1	17:00:00	610_1_2020-06-01
2020-06-04	1001	30	3	06:00:00	30_3_2020-06-04
2020-06-04	1002	330	3	12:00:00	330_3_2020-06-04
2020-06-04	1003	630	3	18:00:00	630_3_2020-06-04
2020-06-03	1001	330	3	12:00:00	330_3_2020-06-03
2020-06-03	1002	630	3	18:00:00	630_3_2020-06-03
2020-06-03	1009	30	3	06:00:00	30_3_2020-06-03
2020-06-05	1001	620	2	17:30:00	620_2_2020-06-05
2020-06-05	1008	20	2	05:30:00	20_2_2020-06-05
2020-06-05	1009	320	2	11:30:00	320_2_2020-06-05
2020-06-02	1001	630	3	18:00:00	630_3_2020-06-02
2020-06-02	1008	30	3	06:00:00	30_3_2020-06-02
2020-06-02	1009	330	3	12:00:00	330_3_2020-06-02
2020-06-06	1001	320	2	15:00:00	320_2_2020-06-06
2020-06-06	1009	20	2	09:00:00	20_2_2020-06-06

2.sql

PreuzmiIzvorni kôd (Text):

SELECT r1.date, e1.id_employees, r1.id_department, s1.id_office, s1.startTime,CONCAT(r1.id_department,'_',s1.id_office,'_',r1.date) AS Uslov
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)
ORDER BY date ASC, id_department ASC

date	id_employees	id_department	id_office	startTime	Uslov
2020-06-07	1001	FD	NULL NULL NULL

to je to, hvala Gjoreski. Rezultat je ono sto sam trazio.
Potrazit cu rijesenje za union all i prouciti.

hvala jos jednom

zivot je moja domovina.

#50

14.06.2020 12:12

Avko

Administrator

Registrovan od:28.05.2014
Postovi:4,708

Predmet:Re: SQL upiti

evo pokrenuo sam u starom wampserver, tamo mi ne radi php >5.5 i tamo UNION ALL radi.
Evo rezultat:

eto, nemoze sve biti idealno. wampserver nema novi php, a UwWamp ne podrzava UNION ALL.
Tako je to u zivotu

zivot je moja domovina.

Stranice (6):1,2,3,4,5,6

Izvoz

Verzija za stampu

Srodne teme

MySQL upiti nad jednom tablom

Ko je online?

Aktivni clanovi:
nema clanova

Razno. veze

Aktivne teme ...

Vijesti (RSS, ...)

Kontakt

Sva vremena su GMT +02:00. Trenutno vrijeme: 6: 32 pm.

Centar za edukaciju-BiH

Glavni meni

Forumi

Linkovi

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

Srodne teme

Ko je online?

Prijavi se

Izdvojeno