MySQL database query

Question

I need help: I ​​want to search from the table how many users (id_uzy) make a reservation, for example, this week, in the next hours that they have reserved (godzina [pl] → hour [en]), and for this hour they find all id_urz.

Example:

• id_uzy-> 1-> hour (9-10) → id_urz (2,3,4)
• id_uzy-> 1-> hour (10-11) → id_urz (2,3,4)
• id_uzy-> 2-> hour (10-11) → id_urz (4,5)
• id_uzy-> 1-> hour (12-13) → id_urz (4,5)

This is my table structure:

id_rez  id_urz  id_uzy  data        godzina  kiedy_rezerwowano
297        4    2     2010-08-23    14-15   2010-08-20 19:07:27
296        4    2     2010-08-23    13-14   2010-08-20 19:07:27
295        4    2     2010-08-23    12-13   2010-08-20 19:07:27
294        4    2     2010-08-23    11-12   2010-08-20 19:07:27
293        26   2     2010-08-23    14-15   2010-08-20 19:07:27
292        26   2     2010-08-23    13-14   2010-08-20 19:07:27
291        26   2     2010-08-23    12-13   2010-08-20 19:07:27
290        26   2     2010-08-23    11-12   2010-08-20 19:07:27
289        4    3     2010-08-30    14-15   2010-08-20 19:07:02
288        4    3     2010-08-30    13-14   2010-08-20 19:07:02
287        8    3     2010-08-30    14-15   2010-08-20 19:07:02
286        8    3     2010-08-30    13-14   2010-08-20 19:07:02
285        1    3     2010-08-23    14-15   2010-08-20 19:06:43
284        1    3     2010-08-23    13-14   2010-08-20 19:06:43
283        1    3     2010-08-23    09-10   2010-08-20 19:06:43
282        1    3     2010-08-23    08-09   2010-08-20 19:06:43
281        6    3     2010-08-23    14-15   2010-08-20 19:06:43
280        6    3     2010-08-23    13-14   2010-08-20 19:06:43
279        6    3     2010-08-23    09-10   2010-08-20 19:06:43
278        6    3     2010-08-23    08-09   2010-08-20 19:06:43

For this table, please help find a solution.

EDIT Also here is Sql to add this to your database:

CREATE TABLE IF NOT EXISTS `prz_rezerwacje` (
  `id_rez` int(5) NOT NULL AUTO_INCREMENT,
  `id_urz` int(6) NOT NULL,
  `id_uzy` int(3) NOT NULL,
  `data` date NOT NULL,
  `godzina` varchar(5) NOT NULL,
  `kiedy_rezerwowano` datetime NOT NULL,
  PRIMARY KEY (`id_rez`),
  KEY `id_uzytkownika_fk` (`id_uzy`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Rezerwacje sprzętu.' AUTO_INCREMENT=298 ;

--
-- Zrzut danych tabeli `prz_rezerwacje`
--

INSERT INTO `prz_rezerwacje` (`id_rez`, `id_urz`, `id_uzy`, `data`, `godzina`, `kiedy_rezerwowano`) VALUES
(297, 4, 2, '2010-08-23', '14-15', '2010-08-20 19:07:27'),
(296, 4, 2, '2010-08-23', '13-14', '2010-08-20 19:07:27'),
(295, 4, 2, '2010-08-23', '12-13', '2010-08-20 19:07:27'),
(294, 4, 2, '2010-08-23', '11-12', '2010-08-20 19:07:27'),
(293, 26, 2, '2010-08-23', '14-15', '2010-08-20 19:07:27'),
(292, 26, 2, '2010-08-23', '13-14', '2010-08-20 19:07:27'),
(291, 26, 2, '2010-08-23', '12-13', '2010-08-20 19:07:27'),
(290, 26, 2, '2010-08-23', '11-12', '2010-08-20 19:07:27'),
(289, 4, 3, '2010-08-30', '14-15', '2010-08-20 19:07:02'),
(288, 4, 3, '2010-08-30', '13-14', '2010-08-20 19:07:02'),
(287, 8, 3, '2010-08-30', '14-15', '2010-08-20 19:07:02'),
(286, 8, 3, '2010-08-30', '13-14', '2010-08-20 19:07:02'),
(285, 1, 3, '2010-08-23', '14-15', '2010-08-20 19:06:43'),
(284, 1, 3, '2010-08-23', '13-14', '2010-08-20 19:06:43'),
(283, 1, 3, '2010-08-23', '09-10', '2010-08-20 19:06:43'),
(282, 1, 3, '2010-08-23', '08-09', '2010-08-20 19:06:43'),
(281, 6, 3, '2010-08-23', '14-15', '2010-08-20 19:06:43'),
(280, 6, 3, '2010-08-23', '13-14', '2010-08-20 19:06:43'),
(279, 6, 3, '2010-08-23', '09-10', '2010-08-20 19:06:43'),
(278, 6, 3, '2010-08-23', '08-09', '2010-08-20 19:06:43');

Answer 1

Something along this line should work:

SELECT pr.id_uzy, pr.godzina, (SELECT pr2.id_urz
                          FROM prz_rezerwacje pr2
                          WHERE pr2.id_uzy = pr.id_uzy
                            AND pr2.godzina = pr.godzina) as id_urz_group
FROM prz_rezerwacje pr;

where . , , (.. ), :

WHERE kiedy_rezerwowano BETWEEN DATE_SUB(NOW(), INTERVAL x DAY) AND NOW();