Centar za edukaciju-BiH

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


Prosiri i skupi Opcije teme
Stranice (6):1,2,3,4,5,6

#21 08.06.2020 12:34
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
Citiraj Gjoreski:
PreuzmiIzvorni kôd (SQL):
  1. SELECT *
  2. FROM tbl_calendar t1
  3. INNER JOIN tbl_citydepartmentoffice t2 ON t1.id_cityDepartmentOffice=t2.Id_cityDeparmentOffice
  4. INNER JOIN tbl_schedule t3 ON t2.id_department = t3.id_department
  5. INNER JOIN (SELECT A.DATE,B.id_office,B.id_department
  6.                   FROM tbl_schedule A
  7.                   INNER JOIN tbl_citydepartmentoffice B ON B.id_department=A.id_department
  8.                   WHERE A.id_employees=1001)
  9. tt ON tt.DATE=t3.DATE AND tt.id_department=t2.id_department AND tt.id_office=t2.id_office


Nažalost nije.

Ja sam malo isprobavao. Imam upit za sve employees i sve datume:

PreuzmiIzvorni kôd (Text):
  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. INNER 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. WHERE r1.date >= DATE('2020-06-01')
  7. ORDER BY r1.date ASC, r1.id_department ASC
  8. limit 0,100;

ako dodam u WHERE AND r1.id_employees=1001, pokaze mi samo za 1001 za sve datume a nevidim druge employeese. Isto tako ne vidim za 7.7. 1001 je FD to mi ne pokaze.

Mozda bi trebalo nesto sa IF THEN ELSE. Ako je id_department > 0 (IsNumeric) onda izvrši upit a ako nije onda neznam.

Zakompliciro sam previše. Ja sam razmisljao da uzmem za Vaska upit pa sa php stavim u array i onda idem od 1 do ubound(array) i pretrazujem za svaki dan po office za ostale. To mi jedino sada pada na pamet.
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 12:35 od strane Avko. ↑  ↓

#22 08.06.2020 12:41
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
Citiraj zxz:
PreuzmiIzvorni kôd (Text):
  1. SELECT  r.name
  2. ,rv.Date
  3. ,rv.id_department
  4. ,CONCAT(rv.id_department,rv.Date) as kljuc
  5. FROM tbl_schedule rv
  6. INNER JOIN tbl_employees  as r ON rv.id_employees=r.id_employees
  7. WHERE  CONCAT(rv.id_department,rv.Date)
  8. IN (SELECT
  9. CONCAT(rv.id_department,rv.Date) as kljuc
  10. FROM tbl_schedule as rv WHERE id_employees=1001
  11. )
  12. AND
  13. rv.id_employees<>1001
pod uslovom da sam pogodio polje broj kancelarija.
U mom slucaju je to id_department


U ovome tvome upitu pokazo mi je sve zaposlenike koji su 7.6. imali Fraj dan s time da je izuzet Vasko 1001
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 12:42 od strane Avko. ↑  ↓

#23 08.06.2020 12:51
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Postovi:10,612


Predmet:Re: SQL upiti
Nije zakoplicirano ali nije dobro objanjeno.
Ja neznam engleski pa mi dodatno komlikuje.
Ja evo naprimjer neznam sa sigurnoscu u koju tabelu se upisuje rad.
Znaci treba opisati tabele i imena polja.
Ja se bar nemogu snaci.
Nije problem upita nego je problem znacenje polja.
Evo vidim odgovorio si mi da i dobio samo slobodne dane za te ljude.
To je pokazao iz razloga sto neznam koja je tabele za unos rada.
Jeli id_department broj kancelarije
Podrška samo putem foruma, jer samo tako i ostali imaju koristi od toga.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 12:53 od strane zxz. ↑  ↓

#24 08.06.2020 13:43
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
Raspored
------------
id zaposlenik
id zgrada
datum

raspored nam govori u kojoj zgradi zaposlenik radi za odredeni datum.

grad
---------
id grad
id kancelarija
id zgrada

u gradu imamo zgrade sa kancelarijama

Kalendar
-----------
datum
id grad

zaposlenik
-------------
id zaposlenik
ime

Kancelarija u zgradi ne radi uvijek isto. Subotom rade samo 2 zaposlenika u kancelariji u kojoj radnim danom rade 3 zaposlenika. Koliko ce smjena biti u nekoj kancelariji odreduje se preko tbl grad.
Zgradi se mijenja id prelaskom na subotu ili nedjelju pa tako kancelarija id 1 u id zgradi 10 radnim danom pocinje raditi od 5 dok nedjeljom pocinje raditi od 6.
zivot je moja domovina.
↑  ↓

#25 08.06.2020 15:47
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Postovi:10,612


Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (MySQL):
  1. SELECT  rv.id_employees
  2. ,r.name
  3. ,rv.Date
  4. ,rv.id_department
  5. ,o.id_office
  6. ,CONCAT(k.id_cityDepartmentOffice,rv.id_department) as kljuc
  7. FROM tbl_schedule as rv
  8. INNER JOIN tbl_employees  as r ON rv.id_employees=r.id_employees
  9. INNER JOIN tbl_calendar as k   ON k.date=rv.date
  10. INNER JOIN tbl_citydepartmentoffice as o   ON o.Id_cityDeparmentOffice=k.id_cityDepartmentOffice
  11. AND rv.id_department=o.id_department
  12. WHERE  o.id_office
  13. IN (SELECT o.id_office
  14. FROM tbl_schedule as rv
  15. INNER JOIN tbl_citydepartmentoffice as o   ON rv.id_department=o.id_department
  16. WHERE rv.id_employees=1001
  17. )
  19. rv.id_employees<>1001
Da budem iskren nisam dovoljno ni procitao.
Gore sam mislio da su prve dvije tabele 1 na 1.
Ovdje je problem kljuca za cetvrtu tabelu i ako je on
IdCityDepartmentOffice i
IdDepartment
onda bi ovo rjesenje trebalo biti dobro.
Nisam testirao.
Podrška samo putem foruma, jer samo tako i ostali imaju koristi od toga.
↑  ↓

#26 08.06.2020 19:36
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,828


Predmet:Re: SQL upiti
Ja opet ne razumem nista sve radim napamet.

Mozemo li ovako:
Prvo uradi cu jedan sql koj ce nam iscitati sve zapise koje nam trebaju a kasnije cemo dodati uslov,evo SQL pogledaj jel ima on sve podatke koje nam trebaju ?

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
↑  ↓

#27 09.06.2020 09:25
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
Citiraj zxz:
PreuzmiIzvorni kôd (MySQL):
  1. SELECT  rv.id_employees
  2. ,r.name
  3. ,rv.Date
  4. ,rv.id_department
  5. ,o.id_office
  6. ,CONCAT(k.id_cityDepartmentOffice,rv.id_department) as kljuc
  7. FROM tbl_schedule as rv
  8. INNER JOIN tbl_employees  as r ON rv.id_employees=r.id_employees
  9. INNER JOIN tbl_calendar as k   ON k.date=rv.date
  10. INNER JOIN tbl_citydepartmentoffice as o   ON o.Id_cityDeparmentOffice=k.id_cityDepartmentOffice
  11. AND rv.id_department=o.id_department
  12. WHERE  o.id_office
  13. IN (SELECT o.id_office
  14. FROM tbl_schedule as rv
  15. INNER JOIN tbl_citydepartmentoffice as o   ON rv.id_department=o.id_department
  16. WHERE rv.id_employees=1001
  17. )
  19. rv.id_employees<>1001
Da budem iskren nisam dovoljno ni procitao.
Gore sam mislio da su prve dvije tabele 1 na 1.
Ovdje je problem kljuca za cetvrtu tabelu i ako je on
IdCityDepartmentOffice i
IdDepartment
onda bi ovo rjesenje trebalo biti dobro.
Nisam testirao.

id_employeesnameDateid_departmentid_officekljuc 
1002Luka2020-06-013101106310 
1003Marko2020-06-016101106610 
1004Ivan2020-06-0120210620 
1005Marta2020-06-013202106320 
1006Smiljan2020-06-016202106620 
1007Mitar2020-06-0130310630 
1008Ahmed2020-06-013303106330 
1009Aleksandar2020-06-016303106630 
1002Luka2020-06-0210110610 
1003Marko2020-06-023101106310 
1004Ivan2020-06-026101106610 
1005Marta2020-06-0220210620 
1006Smiljan2020-06-023202106320 
1007Mitar2020-06-026202106620 
1008Ahmed2020-06-0230310630 
1009Aleksandar2020-06-023303106330 
1002Luka2020-06-036303106630 
1003Marko2020-06-0310110610 
1004Ivan2020-06-033101106310 
1005Marta2020-06-036101106610 
1006Smiljan2020-06-0320210620 
1007Mitar2020-06-033202106320 
1008Ahmed2020-06-036101106610 
1009Aleksandar2020-06-0330310630 
1002Luka2020-06-043303106330 
1003Marko2020-06-046303106630 
1004Ivan2020-06-0410110610 
1005Marta2020-06-043101106310 
1006Smiljan2020-06-046101106610 
1007Mitar2020-06-0420210620 
1008Ahmed2020-06-043202106320 
1009Aleksandar2020-06-046202106620 
1002Luka2020-06-0530310630 
1003Marko2020-06-053303106330 
1004Ivan2020-06-056303106630 
1005Marta2020-06-0510110610 
1006Smiljan2020-06-053101106310 
1007Mitar2020-06-056101106610 
1008Ahmed2020-06-0520210620 
1009Aleksandar2020-06-053202106320 
1006Smiljan2020-06-0610160610 
1007Mitar2020-06-063101606310 
1008Ahmed2020-06-066101606610 
1009Aleksandar2020-06-0620260620 
1007Mitar2020-06-0710170610 
1008Ahmed2020-06-073101706310 
1009Aleksandar2020-06-076101706610 

Pokazuje sve osim Vasko 1001, nije ni točno.

ovako bi trebalo izgledati:

dateid_employeesnameid_departmentid_officestartTime 
2020-06-011001Vasko10105:00:00 
2020-06-011002Luka310111:00:00 
2020-06-011003Marko610117:00:00 
2020-06-021008Ahmed30306:00:00 
2020-06-021009Aleksandar330312:00:00 
2020-06-021001Vasko630318:00:00 
2020-06-031009Aleksandar30306:00:00 
2020-06-031001Vasko330312:00:00 
2020-06-031002Luka630318:00:00 
2020-06-041001Vasko30306:00:00 
2020-06-041002Luka330312:00:00 
2020-06-041003Marko630318:00:00 
2020-06-051008Ahmed20205:30:00 
2020-06-051009Aleksandar320211:30:00 
2020-06-051001Vasko620217:30:00 
2020-06-061009Aleksandar20209:00:00 
2020-06-061001Vasko320215:00:00 
2020-06-071001VaskoFD   
zivot je moja domovina.
↑  ↓

#28 09.06.2020 09:28
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
Citiraj Gjoreski:
Ja opet ne razumem nista sve radim napamet.

Mozemo li ovako:
Prvo uradi cu jedan sql koj ce nam iscitati sve zapise koje nam trebaju a kasnije cemo dodati uslov,evo SQL pogledaj jel ima on sve podatke koje nam trebaju ?

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

evo ištitalo je sve:

dateid_employeesnameid_departmentid_officestartTime 
2020-06-011001Vasko10105:00:00 
2020-06-021002Luka10105:00:00 
2020-06-031003Marko10105:00:00 
2020-06-041004Ivan10105:00:00 
2020-06-051005Marta10105:00:00 
2020-06-011004Ivan20205:30:00 
2020-06-021005Marta20205:30:00 
2020-06-031006Smiljan20205:30:00 
2020-06-041007Mitar20205:30:00 
2020-06-051008Ahmed20205:30:00 
2020-06-041001Vasko30306:00:00 
2020-06-051002Luka30306:00:00 
2020-06-011007Mitar30306:00:00 
2020-06-021008Ahmed30306:00:00 
2020-06-031009Aleksandar30306:00:00 
2020-06-011002Luka310111:00:00 
2020-06-021003Marko310111:00:00 
2020-06-031004Ivan310111:00:00 
2020-06-041005Marta310111:00:00 
2020-06-051006Smiljan310111:00:00 
2020-06-011005Marta320211:30:00 
2020-06-021006Smiljan320211:30:00 
2020-06-031007Mitar320211:30:00 
2020-06-041008Ahmed320211:30:00 
2020-06-051009Aleksandar320211:30:00 
2020-06-031001Vasko330312:00:00 
2020-06-041002Luka330312:00:00 
2020-06-051003Marko330312:00:00 
2020-06-011008Ahmed330312:00:00 
2020-06-021009Aleksandar330312:00:00 
2020-06-011003Marko610117:00:00 
2020-06-021004Ivan610117:00:00 
2020-06-031005Marta610117:00:00 
2020-06-041006Smiljan610117:00:00 
2020-06-051007Mitar610117:00:00 
2020-06-031008Ahmed610117:00:00 
2020-06-051001Vasko620217:30:00 
2020-06-011006Smiljan620217:30:00 
2020-06-021007Mitar620217:30:00 
2020-06-041009Aleksandar620217:30:00 
2020-06-021001Vasko630318:00:00 
2020-06-031002Luka630318:00:00 
2020-06-041003Marko630318:00:00 
2020-06-051004Ivan630318:00:00 
2020-06-011009Aleksandar630318:00:00 
2020-06-061006Smiljan10105:00:00 
2020-06-061009Aleksandar20209:00:00 
2020-06-061007Mitar310111:00:00 
2020-06-061001Vasko320215:00:00 
2020-06-061008Ahmed610117:00:00 
2020-06-071007Mitar10106:00:00 
2020-06-071008Ahmed310112:00:00 
2020-06-071009Aleksandar610118:00:00 
2020-06-071001VaskoFDNULL    NULL  
2020-06-061002LukaFDNULL    NULL  
2020-06-071002LukaFDNULL    NULL  
2020-06-061003MarkoFDNULL    NULL  
2020-06-071003MarkoFDNULL    NULL  
2020-06-061004IvanFDNULL    NULL  
2020-06-071004IvanFDNULL    NULL  
2020-06-061005MartaFDNULL    NULL  
2020-06-071005MartaFDNULL    NULL  
2020-06-071006SmiljanFDNULL    NULL  
zivot je moja domovina.
↑  ↓

#29 09.06.2020 10:11
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,828


Predmet:Re: SQL upiti
Ako je sve uredu ako imamo sva sta nam treba mozes li sad da nam kazes sta treba da se izdvoi iz ova tabela a sta ne treba biti tu pa da uradimo uslov
↑  ↓

#30 09.06.2020 11:52
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,707


Predmet:Re: SQL upiti
dateid_employeesnameid_departmentid_officestartTime 
2020-06-011001Vasko10105:00:00 TREBA
2020-06-021002Luka10105:00:00
2020-06-031003Marko10105:00:00
2020-06-041004Ivan10105:00:00
2020-06-051005Marta10105:00:00
2020-06-011004Ivan20205:30:00
2020-06-021005Marta20205:30:00
2020-06-031006Smiljan20205:30:00
2020-06-041007Mitar20205:30:00
2020-06-051008Ahmed20205:30:00 TREBA
2020-06-041001Vasko30306:00:00 TREBA
2020-06-051002Luka30306:00:00
2020-06-011007Mitar30306:00:00
2020-06-021008Ahmed30306:00:00 TREBA
2020-06-031009Aleksandar30306:00:00
2020-06-011002Luka310111:00:00 TREBA
2020-06-021003Marko310111:00:00
2020-06-031004Ivan310111:00:00
2020-06-041005Marta310111:00:00
2020-06-051006Smiljan310111:00:00
2020-06-011005Marta320211:30:00
2020-06-021006Smiljan320211:30:00
2020-06-031007Mitar320211:30:00
2020-06-041008Ahmed320211:30:00
2020-06-051009Aleksandar320211:30:00 TREBA
2020-06-031001Vasko330312:00:00 TREBA
2020-06-041002Luka330312:00:00 TREBA
2020-06-051003Marko330312:00:00
2020-06-011008Ahmed330312:00:00
2020-06-021009Aleksandar330312:00:00 TREBA
2020-06-011003Marko610117:00:00 TREBA
2020-06-021004Ivan610117:00:00
2020-06-031005Marta610117:00:00
2020-06-041006Smiljan610117:00:00
2020-06-051007Mitar610117:00:00
2020-06-031008Ahmed610117:00:00
2020-06-051001Vasko620217:30:00 TREBA
2020-06-011006Smiljan620217:30:00
2020-06-021007Mitar620217:30:00
2020-06-041009Aleksandar620217:30:00
2020-06-021001Vasko630318:00:00 TREBA
2020-06-031002Luka630318:00:00
2020-06-041003Marko630318:00:00 TREBA
2020-06-051004Ivan630318:00:00
2020-06-011009Aleksandar630318:00:00
2020-06-061006Smiljan10105:00:00
2020-06-061009Aleksandar20209:00:00
2020-06-061007Mitar310111:00:00
2020-06-061001Vasko320215:00:00 TREBA
2020-06-061008Ahmed610117:00:00
2020-06-071007Mitar10106:00:00
2020-06-071008Ahmed310112:00:00
2020-06-071009Aleksandar610118:00:00
2020-06-071001VaskoFDNULL NULL TREBA
2020-06-061002LukaFDNULL NULL
2020-06-071002LukaFDNULL NULL
2020-06-061003MarkoFDNULL NULL
2020-06-071003MarkoFDNULL NULL
2020-06-061004IvanFDNULL NULL
2020-06-071004IvanFDNULL NULL
2020-06-061005MartaFDNULL NULL
2020-06-071005MartaFDNULL NULL
2020-06-071006SmiljanFDNULL NULL


dodao sam na tvoj upot ORDER BY r1.date ASC, s1.id_office ASC
Tako da mi sortira po datumu i office
rezultat:

plava treba
date Ascending 1id_employeesid_departmentid_officestartTime
2020-06-01100110105:00:00
2020-06-011002310111:00:00
2020-06-011003610117:00:00
2020-06-01100420205:30:00
2020-06-011005320211:30:00
2020-06-011006620217:30:00
2020-06-01100730306:00:00
2020-06-011008330312:00:00
2020-06-011009630318:00:00
2020-06-02100210105:00:00
2020-06-021003310111:00:00
2020-06-021004610117:00:00
2020-06-02100520205:30:00
2020-06-021006320211:30:00
2020-06-021007620217:30:00
2020-06-02100830306:00:00
2020-06-021009330312:00:00
2020-06-021001630318:00:00
2020-06-03100310105:00:00
2020-06-031004310111:00:00
2020-06-031005610117:00:00
2020-06-031008610117:00:00
2020-06-03100620205:30:00
2020-06-031007320211:30:00
2020-06-03100930306:00:00
2020-06-031001330312:00:00
2020-06-031002630318:00:00
2020-06-04100410105:00:00
2020-06-041005310111:00:00
2020-06-041006610117:00:00
2020-06-04100720205:30:00
2020-06-041008320211:30:00
2020-06-041009620217:30:00
2020-06-04100130306:00:00
2020-06-041002330312:00:00
2020-06-041003630318:00:00
2020-06-05100510105:00:00
2020-06-051006310111:00:00
2020-06-051007610117:00:00
2020-06-05100820205:30:00
2020-06-051009320211:30:00
2020-06-051001620217:30:00
2020-06-05100230306:00:00
2020-06-051003330312:00:00
2020-06-051004630318:00:00
2020-06-061002FDNULL    NULL 
2020-06-061003FDNULL    NULL 
2020-06-061004FDNULL    NULL 
2020-06-061005FDNULL    NULL 
2020-06-06100610105:00:00
2020-06-061007310111:00:00
2020-06-061008610117:00:00
2020-06-06100920209:00:00
2020-06-061001320215:00:00
2020-06-071001FDNULL    NULL 
2020-06-071002FDNULL    NULL 
2020-06-071003FDNULL    NULL 
2020-06-071004FDNULL    NULL 
2020-06-071005FDNULL    NULL 
2020-06-071006FDNULL    NULL 
2020-06-07100710106:00:00
2020-06-071008310112:00:00
2020-06-071009610118:00:00

znaci ovo treba ostati nakon upita:
date Ascending 1id_employeesid_departmentid_officestartTime
2020-06-01100110105:00:00
2020-06-011002310111:00:00
2020-06-011003610117:00:00
2020-06-02100830306:00:00
2020-06-021009330312:00:00
2020-06-021001630318:00:00
2020-06-03100930306:00:00
2020-06-031001330312:00:00
2020-06-031002630318:00:00
2020-06-04100130306:00:00
2020-06-041002330312:00:00
2020-06-041003630318:00:00
2020-06-05100820205:30:00
2020-06-051009320211:30:00
2020-06-051001620217:30:00
2020-06-06100920209:00:00
2020-06-061001320215:00:00
2020-06-071001FDNULL NULL

Gjoreski , ti samo daj upit a ja cu isprobati i napisati rezultat. Isprobavat ćemo dok ne pogodimo.
Isto vrijedi i za zxz-a. Ti upit a ja rezultat
zivot je moja domovina.
Ovaj post je ureden 3 puta. Posljednja izmjena 09.06.2020 12:01 od strane Avko. ↑  ↓

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

Srodne teme

Ko je online?

Aktivni clanovi:
nema clanova


Kontakt
Sva vremena su GMT +02:00. Trenutno vrijeme: 4: 43 pm.