Gjoreski 05.06.2020 23:38
Predmet:Re: SQL upiti

Postavi baza pa da vidimo ,ovako moram praviti tabeli za svako tvoje pitajne

Avko 07.06.2020 06:24
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');

Gjoreski 07.06.2020 16:32
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)

Gjoreski 07.06.2020 17:36
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) ;

Avko 07.06.2020 17:36
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

Avko 08.06.2020 07:50
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 

Avko 08.06.2020 07:54
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

Avko 08.06.2020 08:04
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 

Gjoreski 08.06.2020 10:20
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

zxz 08.06.2020 11:12
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