I have two tables in a restaurant search done in php. All the information about the restaurant type, facilities, cuisines are in entered to Table2 -'stack' with reference to their restuarant id in the table 1. How do i run a query so that i can get all the restaurant that serve chineese and also serves dinner and also have parking?
This doesn't seems to work:
SELECT DISTINCT restaurant.name, restaurant.place
FROM stack,restaurant
WHERE restaurant.id=stack.rest_id AND stack.value='chineese'
AND stack.value='dinner' AND stack.value='parking'
Here is my table structure
Table1 - **restaurant**
------+----------+----------
id + name + place
------+----------+----------
1 rest1 ny
2 rest2 la
3 rest3 ph
4 rest4 mlp
Table2 - **stack**
------+----------+-------------------------
id + rest_id + type + value
------+----------+-------------------------
1 1 cuisine chinese
2 1 serves breakfast
3 1 facilities party hall
4 1 serves lunch
5 1 serves dinner
6 1 cuisine seafood
7 2 cuisine Italian
8 2 serves breakfast
9 2 facilities parking
10 2 serves lunch
11 2 serves dinner
12 2 cuisine indian
Also tell me if this is the wrong method. I used stack, because cuisine, facilities all can be unlimited as its not defined and very for each.
Given your existing structure, that's quite easy:
SELECT name, place FROM restaurant WHERE id IN (
SELECT rest_id FROM stack
WHERE value IN ('chinese', 'dinner', 'parking')
GROUP BY rest_id
HAVING COUNT(rest_id)=3);
Just make sure that the numeric value given to HAVING COUNT(rest_id) matches the number of values that you are searching for. Here's a simple test case (note that I have added another restaurant, which actually has 'chinese', 'dinner' and 'parking':
CREATE TABLE `restaurant` (
`id` int(11) NOT NULL auto_increment,
`name` VARCHAR(255),
`place` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `stack` (
`id` int(11) NOT NULL auto_increment,
`rest_id` int(11) NOT NULL,
`type` VARCHAR(255),
`value` VARCHAR(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `restaurant` VALUES
(1, 'rest1', 'ny'),
(2, 'rest2', 'la'),
(3, 'rest3', 'ph'),
(4, 'rest4', 'mlp');
INSERT INTO `stack` VALUES
( 1, 1, 'cuisine', 'chinese'),
( 2, 1, 'serves', 'breakfast'),
( 3, 1, 'facilities', 'party hall'),
( 4, 1, 'serves', 'lunch'),
( 5, 1, 'serves', 'dinner'),
( 6, 1, 'cuisine', 'seafood'),
( 7, 2, 'cuisine', 'Italian'),
( 8, 2, 'serves', 'breakfast'),
( 9, 2, 'facilities', 'parking'),
(10, 2, 'serves', 'lunch'),
(11, 2, 'serves', 'dinner'),
(12, 2, 'cuisine', 'indian'),
(13, 3, 'cuisine', 'chinese'),
(14, 3, 'serves', 'breakfast'),
(15, 3, 'facilities', 'parking'),
(16, 3, 'serves', 'lunch'),
(17, 3, 'serves', 'dinner'),
(18, 3, 'cuisine', 'indian');
SELECT name, place FROM restaurant WHERE id IN (
SELECT rest_id FROM stack
WHERE value IN ('chinese', 'dinner', 'parking')
GROUP BY rest_id
HAVING COUNT(rest_id)=3);
+-------+-------+
| name | place |
+-------+-------+
| rest3 | ph |
+-------+-------+
SELECT name, place FROM restaurant WHERE id IN (
SELECT rest_id FROM stack
WHERE value IN ('chinese', 'dinner')
GROUP BY rest_id
HAVING COUNT(rest_id)=2);
+-------+-------+
| name | place |
+-------+-------+
| rest1 | ny |
| rest3 | ph |
+-------+-------+
SELECT name, place FROM restaurant WHERE id IN (
SELECT rest_id FROM stack
WHERE value IN ('parking', 'hellipad')
GROUP BY rest_id
HAVING COUNT(rest_id)=2);
Empty set (0.00 sec)
Alternatively, you could create related tables, like this (but this is probably not the best structure):
---> facility
restaurant ---> restaurant_has_facility ---|
---> facility_type
The query is almost the same, you just need your subquery to produce the appropriate join:
SELECT restaurant_name, restaurant_place FROM (
SELECT
r.id AS restaurant_id,
r.name AS restaurant_name,
r.place AS restaurant_place,
ft.name AS facility_name
FROM restaurant AS r
JOIN restaurant_has_facility AS rf ON rf.restaurant_id = r.id
JOIN facility_type AS ft ON ft.id = rf.facility_type_id
ORDER BY r.id, ft.name) AS tmp
WHERE facility_name IN ('chinese', 'dinner', 'parking')
GROUP BY tmp.restaurant_id
HAVING COUNT(tmp.restaurant_id)=3;
Here's some sample SQL for the above structure:
CREATE TABLE `restaurant` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`place` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE `facility` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `facility_type` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `restaurant_has_facility` (
`restaurant_id` INT UNSIGNED NOT NULL ,
`facility_id` INT UNSIGNED NOT NULL ,
`facility_type_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`restaurant_id`, `facility_id`, `facility_type_id`) ,
INDEX `fk_restaurant_has_facility_restaurant` (`restaurant_id` ASC) ,
CONSTRAINT `fk_restaurant_has_facility_restaurant`
FOREIGN KEY (`restaurant_id` )
REFERENCES `restaurant` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
INSERT INTO `restaurant` VALUES
(1, 'rest1', 'ny'),
(2, 'rest2', 'la'),
(3, 'rest3', 'ph'),
(4, 'rest4', 'mlp');
INSERT INTO `facility` VALUES
(1, 'cuisine'),
(2, 'serves'),
(3, 'facilities');
INSERT INTO `facility_type` VALUES
(1, 'chinese'),
(2, 'breakfast'),
(3, 'party hall'),
(4, 'lunch'),
(5, 'dinner'),
(6, 'seafood'),
(7, 'Italian'),
(8, 'parking'),
(9, 'indian');
INSERT INTO `restaurant_has_facility` VALUES
(1, 1, 1),
(1, 2, 2),
(1, 3, 3),
(1, 2, 4),
(1, 2, 5),
(1, 1, 6),
(2, 1, 7),
(2, 2, 2),
(2, 3, 8),
(2, 2, 4),
(2, 2, 5),
(2, 1, 9),
(3, 1, 1),
(3, 2, 5),
(3, 3, 8),
(3, 2, 4),
(3, 2, 2),
(3, 1, 9);