I need help in preparing a MYSQL query.
I have a data set as follows:
-- Table structure for table `property_guest_type`
CREATE TABLE IF NOT EXISTS `property_guest_type` (
`id` int(10) unsigned NOT NULL,
`property_id` int(10) unsigned NOT NULL,
`singular` varchar(20) NOT NULL,
`plural` varchar(20) NOT NULL,
`min_age` tinyint(3) unsigned NOT NULL DEFAULT '0',
`max_age` tinyint(3) unsigned NOT NULL DEFAULT '0',
`is_active` tinyint(3) unsigned NOT NULL DEFAULT '0',
`date_created` datetime NOT NULL,
`date_modified` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=614 DEFAULT CHARSET=utf8;
-- Dumping data for table `property_guest_type`
INSERT INTO `property_guest_type` (`id`, `property_id`, `singular`, `plural`, `min_age`, `max_age`, `is_active`, `date_created`, `date_modified`) VALUES
(566, 41, 'Adult', 'Adults', 18, 110, 1, '2018-08-16 10:54:19', '2018-08-16 10:54:19'),
(571, 41, 'Child', 'Children', 3, 17, 1, '2018-08-16 10:55:37', '2018-08-16 10:54:19'),
(574, 41, 'Infant', 'Infants', 0, 2, 1, '2018-08-16 10:56:31', '2018-08-16 10:54:19');
CREATE TABLE IF NOT EXISTS `property_occupancy_rule` (
`id` int(10) unsigned NOT NULL,
`property_id` int(10) unsigned NOT NULL,
`count` tinyint(4) NOT NULL,
`operator` enum('min','max') NOT NULL,
`guest_types` varchar(45) NOT NULL,
`date_created` datetime NOT NULL,
`date_modified` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1605 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `property_occupancy_rule`
--
INSERT INTO `property_occupancy_rule` (`id`, `property_id`, `count`, `operator`, `guest_types`, `date_created`, `date_modified`) VALUES
(1456, 41, 1, 'min', '566', '2018-08-16 10:57:06', '2018-08-16 10:57:06'),
(1466, 41, 4, 'max', '566', '2018-08-16 11:05:02', '2018-08-16 10:57:06'),
(1468, 41, 6, 'max', '566+571', '2018-08-16 11:05:30', '2018-08-16 10:57:06'),
(1470, 41, 8, 'max', '566+571+574', '2018-08-16 11:05:49', '2018-08-16 10:57:06'),
(1469, 41, 8, 'max', '566+574', '2018-08-16 11:05:40', '2018-08-16 10:57:06'),
(1467, 41, 5, 'max', '571', '2018-08-16 11:05:16', '2018-08-16 10:57:06'),
(1471, 41, 7, 'max', '571+574', '2018-08-16 11:06:03', '2018-08-16 10:57:06'),
(1465, 41, 2, 'max', '574', '2018-08-16 11:02:56', '2018-08-16 10:57:06');
...and here's an SQL fiddle of same, together with a sample JOIN query I have tried.
: http://sqlfiddle.com/#!9/926f9/5
So, I have two tables in my schema which stores the guest_types and then accordingly the corresponding occupancy rules in another table.
For example I have guest types as "Adult, Children, Infants" which have a certain max/min ages.
And as per occupancy rules, we can say a Hotel / Proeprty can have max 2 adults and 3 children and 1 infant.
On the front-end side, we have adults, children and infant dropdowns with multi adding facility, so I can add as many children or adults I like with their ages.
And I need to search these two tables to get the property id's based on the search criteria.
I have created a JOIN bsed query which is looking up the adults fine, but I want to combine or kind of make a one query which can search all adults/children and infants and analyze if it is right property to show or not.
Please advise how I can achive the same in single MySQL query?