Highly filtered search based on multiple conditions

Question

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.

Answer 1

try this..

SELECT r.name FROM restaurant as r JOIN stack as s ON r.id=s.rest_id WHERE s.value='chinese' AND s.value='dinner' AND s.value='parking';

Answer 2

The only way I know of doing something like this is to "pivot" the data - essentially making the rows into columns. Eg. You currently have 1 row for each of your values, but you ideally want 1 row for each restaurant so you can query the values.

The bad news is that you'll need to know all of the possible values in your select statement, or you'll need to use cursors.

The following should give some idea how to create the pivot:

SELECT        
  rest_id, 
  MAX(CASE WHEN s.value = 'chinese' THEN 1 ELSE 0 END) AS chinese, 
  MAX(CASE WHEN s.value = 'breakfast' THEN 1 ELSE 0 END) AS breakfast, 
  MAX(CASE WHEN s.value = 'party hall' THEN 1 ELSE 0 END) AS [party hall], 
  MAX(CASE WHEN s.value = 'lunch' THEN 1 ELSE 0 END) AS lunch,
  MAX(CASE WHEN s.value = 'dinner' THEN 1 ELSE 0 END) AS dinner, 
  MAX(CASE WHEN s.value = 'seafood' THEN 1 ELSE 0 END) AS seafood,
  MAX(CASE WHEN s.value = 'Italian' THEN 1 ELSE 0 END) AS Italian,  
  MAX(CASE WHEN s.value = 'parking' THEN 1 ELSE 0 END) AS parking, 
  MAX(CASE WHEN s.value = 'Indian' THEN 1 ELSE 0 END) AS indian 
FROM            
  stack AS s
GROUP BY 
  rest_id

This will create a table that looks like:

rest_id | chinese | breakfast | party hall | lunch | dinner | seafood | Italian | parking | indian
--------+---------+-----------+------------+-------+--------+---------+---------+---------+-------
   1    |   1     |     1     |      1     |   1   |    1   |    1    |    0    |    0    |    0
   2    |   0     |     1     |      0     |   1   |    1   |    0    |    1    |    1    |    1

From this table it's then quite a simple join to get the restaurants which have particular features.

For example:

SELECT restaurant.name, restaurant.place FROM restaurant LEFT JOIN
  (SELECT        
    rest_id, 
    MAX(CASE WHEN s.value = 'chinese' THEN 1 ELSE 0 END) AS chinese, 
    MAX(CASE WHEN s.value = 'breakfast' THEN 1 ELSE 0 END) AS breakfast, 
    MAX(CASE WHEN s.value = 'party hall' THEN 1 ELSE 0 END) AS [party hall], 
    MAX(CASE WHEN s.value = 'lunch' THEN 1 ELSE 0 END) AS lunch,
    MAX(CASE WHEN s.value = 'dinner' THEN 1 ELSE 0 END) AS dinner, 
    MAX(CASE WHEN s.value = 'seafood' THEN 1 ELSE 0 END) AS seafood,
    MAX(CASE WHEN s.value = 'Italian' THEN 1 ELSE 0 END) AS Italian,  
    MAX(CASE WHEN s.value = 'parking' THEN 1 ELSE 0 END) AS parking, 
    MAX(CASE WHEN s.value = 'Indian' THEN 1 ELSE 0 END) AS indian 
  FROM            
    stack AS s
  GROUP BY 
    rest_id) AS features
ON 
  restaurant.id=features.rest_id
WHERE 
  features.chinese=1 and features.dinner=1 and features.parking=1

Answer 3

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);