I have this structure of table Diary:
CREATE TABLE Diary
(
[IdDiary] bigint,
[IdDay] numeric(18,0)
);
INSERT INTO Diary ([IdDiary], [IdDay])
values
(51, 1),
(52, 2),
(53, 5);
And this other structure for table DiaryTimetable:
CREATE TABLE DiaryTimetable
(
[IdDiary] bigint,
[Hour] varchar(50)
);
INSERT INTO DiaryTimetable ([IdDiary], [Hour])
VALUES
(51, '09:00'),
(51, '09:30'),
(51, '10:00'),
(51, '10:30'),
(51, '11:00'),
(51, '11:30'),
(52, '11:00'),
(52, '11:30'),
(52, '12:00'),
(52, '12:30'),
(52, '13:00'),
(52, '13:30'),
(53, '15:00'),
(53, '15:30'),
(53, '16:00'),
(53, '16:30');
The table Diary contains an IdDiary and the IdDay is the number of day, for example:
Monday --> 1
Tuesday --> 2
Wednesday --> 3
Thursday --> 4
Friday --> 5
Saturday --> 6
Sunday --> 7
The table DiaryTimetable contains the iddiary, and the hour. I want want to get the max hour and the min hour in the table DiaryTimetable for each day appears in the Diary table, If I put this query the result will be only the max hour and the min hour for all the query:
select MAX(Hour), MIN(Hour) from DiaryTimetable
inner join Diary on
DiaryTimetable.IdDiary = Diary.IdDiary
The result for wat I need will be something like that:
IdDiary IdDay Min Hour Max Hour
----- ----- -------- ---------
51 1 09:00 11:30
52 2 11:00 13:30
53 5 15:00 16:30
How can I get this, thanks?
You just missed
Group by
in your query.You use a
GROUP BY
clause:Demo here
You just need to add a
GROUP BY
to your query:Then the
MAX
andMIN
command just use the Values from your Diary Table.As in a comment you say that this is just an example for you to learn, I'd like to add another answer.
Given that there are no duplicate records in
Diary
, its two columnsIdDiary
andIdDay
are the fields you want additional information on (the min and max hour). So all you have to do is get this information joined to the table:Sticking to this technique has two advantages:
city
, a tablepark
and a tablelake
. You want to know how many lakes and the sum of park area per city. As shown you'd join the cities with the lake count and the park area sum. If you joined all tables first instead, you'd have a combination of lakes and parks that are actually really unrelated, and it would be hard or impossible to segregate the desired results from this data slush somehow.