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:

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 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');

+1 for a decent explanation of what you're after and for translating some of the column names for us to make it possible! — Will A
– Will A, Commented Aug 20, 2010 at 17:09
Define the start and end days of the week. Some say Monday is the start of the week, others say Sunday. Thanks. — XstreamINsanity
– XstreamINsanity, Commented Aug 20, 2010 at 17:24
Right for this example are 2 date with 2 mondays. So select only from 23/08/2010-28/08/2010 — WooCaSh
– WooCaSh, Commented Aug 20, 2010 at 17:27
So a week is 6 days and the week starts on Mondays. So the following weeks would be 23-28, 30-4, 6-11, etc? Just want to know so when we construct the query it's universal. — XstreamINsanity
– XstreamINsanity, Commented Aug 20, 2010 at 17:30
on site calendar will be without Saturdays and Sundays so: date>=23 and <28 — WooCaSh
– WooCaSh, Commented Aug 20, 2010 at 17:39

OpenDataAlex · Accepted Answer · 2010-09-10 15:22:54Z

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();

answered Sep 10, 2010 at 15:22

OpenDataAlex

1,4755 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query to Database MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related