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
 Prikazi prvu neprocitanu poruku

#11 05.06.2020 23:38
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
Postavi baza pa da vidimo ,ovako moram praviti tabeli za svako tvoje pitajne
↑  ↓

#12 07.06.2020 06:24
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
-- phpMyAdmin SQL Dump
-- version 4.5.4.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 03, 2020 at 12:14 PM
-- Server version: 5.7.11
-- PHP Version: 5.6.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `testbaza`
--

-- --------------------------------------------------
------

--
-- Table structure for table `tbl_calendar`
--

CREATE TABLE `tbl_calendar` (
`date` date NOT NULL,
`id_cityDepartmentOffice` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_calendar`
--

INSERT INTO `tbl_calendar` (`date`, `id_cityDepartmentOffice`) VALUES
('2020-06-01', 106),
('2020-06-02', 106),
('2020-06-03', 106),
('2020-06-04', 106),
('2020-06-05', 106),
('2020-06-06', 606),
('2020-06-07', 706);

-- --------------------------------------------------
------

--
-- Table structure for table `tbl_citydepartmentoffice`
--

CREATE TABLE `tbl_citydepartmentoffice` (
`Id_cityDeparmentOffice` int(11) NOT NULL,
`id_department` int(11) NOT NULL,
`id_office` int(11) NOT NULL,
`startTime` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_citydepartmentoffice`
--

INSERT INTO `tbl_citydepartmentoffice` (`Id_cityDeparmentOffice`, `id_department`, `id_office`, `startTime`) VALUES
(106, 10, 1, '05:00:00'),
(106, 20, 2, '05:30:00'),
(106, 30, 3, '06:00:00'),
(106, 310, 1, '11:00:00'),
(106, 320, 2, '11:30:00'),
(106, 330, 3, '12:00:00'),
(106, 610, 1, '17:00:00'),
(106, 620, 2, '17:30:00'),
(106, 630, 3, '18:00:00'),
(606, 10, 1, '05:00:00'),
(606, 20, 2, '09:00:00'),
(606, 310, 1, '11:00:00'),
(606, 320, 2, '15:00:00'),
(606, 610, 1, '17:00:00'),
(706, 10, 1, '06:00:00'),
(706, 310, 1, '12:00:00'),
(706, 610, 1, '18:00:00');

-- --------------------------------------------------
------

--
-- Table structure for table `tbl_employees`
--

CREATE TABLE `tbl_employees` (
`id_employees` int(11) NOT NULL,
`name` text NOT NULL,
`adress` text NOT NULL,
`phone` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_employees`
--

INSERT INTO `tbl_employees` (`id_employees`, `name`, `adress`, `phone`) VALUES
(1001, 'Vasko', '', ''),
(1002, 'Luka', '', ''),
(1003, 'Marko', '', ''),
(1004, 'Ivan', '', ''),
(1005, 'Marta', '', ''),
(1006, 'Smiljan', '', ''),
(1007, 'Mitar', '', ''),
(1008, 'Ahmed', '', ''),
(1009, 'Aleksandar', '', ''),
(1010, 'Avko', '', '');

-- --------------------------------------------------
------

--
-- Table structure for table `tbl_schedule`
--

CREATE TABLE `tbl_schedule` (
`id_employees` int(11) NOT NULL,
`id_department` text NOT NULL,
`date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_schedule`
--

INSERT INTO `tbl_schedule` (`id_employees`, `id_department`, `date`) VALUES
(1001, '10', '2020-06-01'),
(1001, '630', '2020-06-02'),
(1001, '330', '2020-06-03'),
(1001, '30', '2020-06-04'),
(1001, '620', '2020-06-05'),
(1001, '320', '2020-06-06'),
(1001, 'FD', '2020-06-07'),
(1002, '310', '2020-06-01'),
(1002, '10', '2020-06-02'),
(1002, '630', '2020-06-03'),
(1002, '330', '2020-06-04'),
(1002, '30', '2020-06-05'),
(1002, 'FD', '2020-06-06'),
(1002, 'FD', '2020-06-07'),
(1003, '610', '2020-06-01'),
(1003, '310', '2020-06-02'),
(1003, '10', '2020-06-03'),
(1003, '630', '2020-06-04'),
(1003, '330', '2020-06-05'),
(1003, 'FD', '2020-06-06'),
(1003, 'FD', '2020-06-07'),
(1004, '20', '2020-06-01'),
(1004, '610', '2020-06-02'),
(1004, '310', '2020-06-03'),
(1004, '10', '2020-06-04'),
(1004, '630', '2020-06-05'),
(1004, 'FD', '2020-06-06'),
(1004, 'FD', '2020-06-07'),
(1005, '320', '2020-06-01'),
(1005, '20', '2020-06-02'),
(1005, '610', '2020-06-03'),
(1005, '310', '2020-06-04'),
(1005, '10', '2020-06-05'),
(1005, 'FD', '2020-06-06'),
(1005, 'FD', '2020-06-07'),
(1006, '620', '2020-06-01'),
(1006, '320', '2020-06-02'),
(1006, '20', '2020-06-03'),
(1006, '610', '2020-06-04'),
(1006, '310', '2020-06-05'),
(1006, '10', '2020-06-06'),
(1006, 'FD', '2020-06-07'),
(1007, '30', '2020-06-01'),
(1007, '620', '2020-06-02'),
(1007, '320', '2020-06-03'),
(1007, '20', '2020-06-04'),
(1007, '610', '2020-06-05'),
(1007, '310', '2020-06-06'),
(1007, '10', '2020-06-07'),
(1008, '330', '2020-06-01'),
(1008, '30', '2020-06-02'),
(1008, '610', '2020-06-03'),
(1008, '320', '2020-06-04'),
(1008, '20', '2020-06-05'),
(1008, '610', '2020-06-06'),
(1008, '310', '2020-06-07'),
(1009, '630', '2020-06-01'),
(1009, '330', '2020-06-02'),
(1009, '30', '2020-06-03'),
(1009, '620', '2020-06-04'),
(1009, '320', '2020-06-05'),
(1009, '20', '2020-06-06'),
(1009, '610', '2020-06-07');
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 07.06.2020 06:27 od strane Avko. ↑  ↓

#13 07.06.2020 16:32
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
Avko
Pitanje: Treba napisati upit: tko je sve radio sa Vaskom u kancelariji?

Sad imam ja par pitajna
1. Trebaju ti sve radnike koi su radili u ista kancalarija sa Vasko za dani kad je vasko radio?
Jesam te razumeo ispravno?

Ako je kako sam ja razumeo onda ovaj primer kako bi trebalo da izgleda rezultat prikazuje vise podataka,
a to se vidi od kolona id_office ( Vasko je radio samo u id_office 1 a ovde ima jos neki kancalarija)
↑  ↓

#14 07.06.2020 17:36
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
evo pogledaj jeli ovo tvoj rezultat

PreuzmiIzvorni kôd (SQL):
  1. SELECT * FROM tbl_schedule
  2. INNER JOIN tbl_citydepartmentoffice ON tbl_citydepartmentoffice.id_department=tbl_schedule.id_department
  3. WHERE DATE IN (SELECT DATE
  4.                FROM tbl_schedule
  5.                INNER JOIN tbl_citydepartmentoffice ON tbl_citydepartmentoffice.id_department=tbl_schedule.id_department
  6.                WHERE tbl_schedule.id_employees=1001)
  7. AND id_office IN (SELECT id_office
  8.                   FROM tbl_schedule
  9.                   INNER JOIN tbl_citydepartmentoffice ON tbl_citydepartmentoffice.id_department=tbl_schedule.id_department
  10.                   WHERE tbl_schedule.id_employees=1001) ;
↑  ↓

#15 07.06.2020 17:36
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti






1.6. Vasko 1001 radi u idDepartment 10, a idDepartment 10 za 1.6. je office 1. Vasko radi od 5:00 do 11:00
Tko još radi u office 1 za 1.6.?
Za 1.6. u office 1 još radi:
-1002 od 11:00 do 17:00
-1003 od 17:00 do 23:00

za 2.6.:

Vasko ima idDepartment 630
U tblCalendar za 2.6. IdCityDepartmentOffice je 0106

idDepartment 630 + idCityDepartmentOffice 0106 => idOffice 3

Tko ima idOffice 3 za date 2.6. ?

tblCalendar za 2.6. je idCityDepartmentOffice 0106

u tblCityDepartmentOffice za office 3 i idCitiDepatmentOffice 0106 => idDepartment 30 i idDepartment 330

Tko ima za 2.6. idDepartment 30 i idDepartment 330 ?

u tblSchedule idDepartment 30 i idDepartment 330 za 2.6. imaju employment 1008 i 1009

itd
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 07.06.2020 17:38 od strane Avko. ↑  ↓

#16 08.06.2020 07:50
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
rezultat upita, crveno bi trebalo biti za 1.6. i Vasko

id_employeesid_departmentdateId_cityDeparmentOfficeid_departmentid_officestartTime 
1001102020-06-0110610105:00:00 
1001102020-06-0160610105:00:00 
1001102020-06-0170610106:00:00 
10016302020-06-02106630318:00:00 
10013302020-06-03106330312:00:00 
1001302020-06-0410630306:00:00 
10016202020-06-05106620217:30:00 
10013202020-06-06106320211:30:00 
10013202020-06-06606320215:00:00 
10023102020-06-01106310111:00:00 
10023102020-06-01606310111:00:00 
10023102020-06-01706310112:00:00 
1002102020-06-0210610105:00:00 
1002102020-06-0260610105:00:00 
1002102020-06-0270610106:00:00 
10026302020-06-03106630318:00:00 
10023302020-06-04106330312:00:00 
1002302020-06-0510630306:00:00 
10036102020-06-01106610117:00:00 
10036102020-06-01606610117:00:00 
10036102020-06-01706610118:00:00 
10033102020-06-02106310111:00:00 
10033102020-06-02606310111:00:00 
10033102020-06-02706310112:00:00 
1003102020-06-0310610105:00:00 
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 07:51 od strane Avko. ↑  ↓

#17 08.06.2020 07:54
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
rezultat bi trebao pokazati ovo:
dateid_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-071001FDnullnull

FD = freeday
zivot je moja domovina.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 07:54 od strane Avko. ↑  ↓

#18 08.06.2020 08:04
Avko Van mreze
Administrator
Registrovan od:28.05.2014
Postovi:4,807


Predmet:Re: SQL upiti
Ja sam uspio za jedan datum:

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
  5.     ON (k1.id_cityDepartmentOffice = s1.Id_cityDeparmentOffice AND r1.id_department = s1.id_department)
  6. INNER JOIN tbl_employees AS e1 ON r1.id_employees=e1.id_employees
  7. WHERE r1.date = DATE('2020-06-01')
  8.         AND s1.id_office =(
  9.                                     SELECT s2.id_office
  10.                                     FROM tbl_schedule as r2
  11.                                     INNER JOIN tbl_calendar AS k2 ON r2.date = k2.date
  12.                                     INNER JOIN tbl_citydepartmentoffice AS s2
  13.                                             ON (k2.id_cityDepartmentOffice = s2.Id_cityDeparmentOffice
  14.                                                   AND r2.id_department = s2.id_department)
  15.                                     INNER JOIN tbl_employees AS e2 ON r2.id_employees=e2.id_employees
  16.                                     WHERE r2.id_employees='1001' AND r2.date = DATE('2020-06-01'))

rezultat:

dateid_employeesid_departmentid_officestartTime 
2020-06-01100110105:00:00 
2020-06-011002310111:00:00 
2020-06-011003610117:00:00 
zivot je moja domovina.
↑  ↓

#19 08.06.2020 10:20
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Postovi:1,854


Predmet:Re: SQL upiti
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
↑  ↓

#20 08.06.2020 11:12
zxz Van mreze
Administrator
Registrovan od:03.02.2009
Postovi:10,642


Predmet:Re: SQL upiti
PreuzmiIzvorni kôd (MySQL):
  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. INNER JOIN tbl_calendar as k   ON k.date=rv.date
  8. INNER JOIN tbl_citydepartmentoffice as o   ON o.Id_cityDeparmentOffice=k.id_cityDepartmentOffice
  9. WHERE  CONCAT(rv.id_department,rv.Date)
  11. CONCAT(rv.id_department,rv.Date) as kljuc
  12. FROM tbl_schedule as rv WHERE id_employees=1001
  13. )
  15. rv.id_employees<>1001

pod uslovom da sam pogodio polje broj kancelarija.
U mom slucaju je to id_department
Podrska samo putem foruma, jer samo tako i ostali imaju koristi od toga.
Ovaj post je ureden 1 puta. Posljednja izmjena 08.06.2020 11:45 od strane zxz. ↑  ↓

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

Srodne teme

Ko je online?

Aktivni clanovi:
nema clanova


Kontakt
Sva vremena su GMT +01:00. Trenutno vrijeme: 12: 07 am.