Query to Database MySQL

Question

I need Yoour help: I want search from table how many users(id_uzy) makes reservation on example this week, next which hours they reserved (godzina[pl] -> hour[en]) and for this hours find all id_urz.

Example:

1. id_uzy->1->hour(9-10)-->id_urz(2,3,4)
2. id_uzy->1->hour(10-11)-->id_urz(2,3,4)
3. id_uzy->2->hour(10-11)-->id_urz(4,5)
4. 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 me find 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;

You can then build a where statement to filter as needed. For instance, if you want the query to run for a dynamic week (i.e. run it for a report to be generated), you could use this:

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