-1

I have a hotel table and and a visits table for those who visit the hotel. How do I join the table without showing the same hotel more than once and also show the date of the last person who visited with the condition that the last visit was after 2021-01-01 9:00:00

What I want:

+------+---------------------+
| name |    last_visited     |
+------+---------------------+
| red  | 2021-01-01 09:34:00 |
| blue | 2021-01-01 10:04:00 |
+------+---------------------+

create table hotels (
  id int auto_increment primary key,
  name varchar(32)
);

create table visits (
  hotel int not null, FOREIGN KEY (hotel) REFERENCES hotels (id),
  customer varchar(32) not null,
  date_visited datetime not null
);

INSERT INTO hotels (name) values ('red'), ('blue'), ('green');
INSERT INTO visits (hotel, customer, date_visited) values
(1, 'bob', '2021-01-01 09:00:00'),
(1, 'james', '2021-01-01 09:05:00'),
(1, 'billy', '2021-01-01 09:34:00'), 
(2, 'sarah', '2021-01-01 09:34:00'), 
(2, 'heather', '2021-01-01 10:04:00'), 
(3, 'glenn', '2021-01-01 07:32:00'),
(3, 'jonah', '2021-01-01 08:41:00'), 
(3, 'amy', '2021-01-01 8:59:00');

My attempt

select h.name, v.date_visited as last_visited
from hotels h
left join visits v
on h.id = v.hotel
WHERE v.date_visited > '2021-01-01 9:00:00';
Yuri
  • 89
  • 1
  • 1
  • 9

1 Answers1

1

use aggregate function max()

select h.name, max(v.date_visited )as last_visited
from hotels h
join visits v
on h.id = v.hotel
WHERE v.date_visited > '2021-01-01 9:00:00'
group by h.name
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63