MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `vi_bookings` (
`id` int(11) NOT NULL,
`propertie_id` int(11) NOT NULL,
`rooms_id` int(11) NOT NULL,
`block_name` varchar(256) NOT NULL,
`check_in` date NOT NULL,
`check_out` date NOT NULL,
`status` tinyint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert records

INSERT INTO `vi_bookings` (`id`, `propertie_id`, `rooms_id`, `block_name`, `check_in`, `check_out`, `status`) VALUES
(1, 33, 5, 'BeachHouse', '2022-09-01', '2022-09-04', 3),
(2, 33, 5, 'BeachHouse', '2022-09-04', '2022-09-06', 2),
(3, 33, 5, 'BeachHouse', '2022-11-28', '2022-12-04', 1),
(4, 33, 5, 'BeachHouse', '2022-12-04', '2022-12-14', 13),
(5, 44, 6, 'C3', '2022-09-24', '2022-09-26', 2),
(6, 44, 16, 'C3', '2022-09-26', '2022-09-29', 13),
(7, 46, 12, '8BR', '2022-12-26', '2023-01-11', 2),
(8, 46, 12, '8BR', '2023-01-01', '2023-01-11', 1),
(9, 47, 4, 'Skye', '2022-12-21', '2023-01-01', 3),
(10, 47, 4, 'Skye', '2023-01-01', '2023-01-03', 1);

SELECT o.propertie_id,o.rooms_id,o.block_name,o.check_in,o.check_out,o.status 
FROM vi_bookings o
WHERE EXISTS
(
 SELECT 1 FROM vi_bookings o2
 WHERE o2.`propertie_id` = o.`propertie_id`
 AND o2.`rooms_id` = o.`rooms_id`
 AND o2.`block_name` = o.`block_name`
 AND o2.check_in < o.check_out AND o2.check_out > o.check_in
 AND o2.id != o.id
)
AND o.status NOT IN (4,10)
ORDER BY o.`propertie_id`, o.`block_name`, o.check_in

Records: 10  Duplicates: 0  Warnings: 0

propertie_id	rooms_id	block_name	check_in	check_out	status
46	12	8BR	2022-12-26	2023-01-11	2
46	12	8BR	2023-01-01	2023-01-11	1