MySQL: Select Record If Every Search Criteria Is Found in One of the Child Records

Question

In this case, I have 3 tables: hotels, categories, hotel_category_infos.

Table "hotels": A list of all hotels.

-----------------------
| id | name           |
-----------------------
|  1 | Hotel England  |
|  2 | Hotel Scotland |
|  3 | Hotel Ireland  |
|  4 | Hotel Norway   |
-----------------------

Table "categories": A list of the accessibility categories (concerning a hotel's accessibility for handicapped people). Possible categories a hotel can be assigned to, even more than once.

--------------------------------------------------
| id | name                                      |
--------------------------------------------------
|  1 | Ground floor without steps                |
|  2 | Washing facilities wheelchair accessible  |
|  3 | Special infrastructure on location        |
--------------------------------------------------

Table "hotel_category_infos": The actual assignments of a category to a hotel, with an additional precise description for a category for this hotel.

-----------------------------------------------------------------------
| id | hotel_id | cat_id | description                                |
-----------------------------------------------------------------------
|  1 |        1 |      1 | No steps, except the one to the bla bla... |
|  2 |        1 |      2 | The entrance to the shower is 56cm wide... |
|  3 |        2 |      1 | The ramp at the entrance is 5% steep       |
|  4 |        3 |      1 | Except on the terrace                      |
|  5 |        3 |      2 | Ask for rooms in the first floor           |
|  6 |        3 |      3 | A indoor swimming pool is available for... | 
-----------------------------------------------------------------------

What I would like to do:

In a search form all categories are listed as a checkbox, so after sending the form, I have an array with all the checked category-ids. I would like to list all hotels that have an assignment to every of these categories (not only one of them).

What I have tried:

e.g. select all hotels which have both additional information on "Ground Floor without steps" and "washing facilities"

SELECT 
    hotels.id 
FROM 
    hotels INNER JOIN hotel_category_infos ON         
        hotel_category_infos.hotel_id = hotels.id AND 
    hotel_category_infos.cat_id IN (1,2)

But the IN()-part suggests that only ONE of the categories must match, not both. What am I supposed to change in the SQL-statement?

Be careful with this, but take a look at dynamic sql: http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. It will allow you to dynamically generate statements, but will be prone to sql injection. If you choose to go this route (there are other options as well), make sure you have some sort of validation happening on the incoming data — Russell Uhl, Feb 17 '15 at 16:57

score 0 · Answer 1 · answered Feb 17 '15 at 16:57

0

You need to do two set comparisons because no single row of hotel_category_infos is going to have both cat_id = 1 and cat_id = 2.

SELECT id
FROM hotels
WHERE id IN (SELECT hotel_id FROM hotel_category_infos WHERE cat_id = 1)
    AND id IN (SELECT hotel_id FROM hotel_category_infos WHERE cat_id = 2)

I eliminated the join completely here because it's not necessary given the field you're returning.

answered Feb 17 '15 at 16:57

Bacon Bits

30,782
5
59
66

this is only valid for that one specific example, not for the problem in general – Russell Uhl Feb 17 '15 at 16:59
1

@RussellUhl Obviously. I'm explaining why he's not getting the right results, not how to write the code. I'm solving his problem, not writing his application. – Bacon Bits Feb 17 '15 at 17:01

score 0 · Accepted Answer · answered Feb 17 '15 at 17:09

Please note that this is not in any way valid mysql syntax. It will, however, hopefully give you a dynamic solution to your problem. Also note that you will need to validate your incoming data to prevent sql injection.

DECLARE p_Statement NVARCHAR(MAX)
SELECT p_Statement = 'SELECT id FROM hotels WHERE 1 = 1 '

for each p_catID in SelectedCatIds (loop) :
    SELECT p_Statement = p_Statement + 'AND id IN (SELECT hotel_id FROM hotel_category_infos WHERE cat_id = p_catID '
(end loop)

PREPARE stmt FROM p_Statement
EXECUTE stmt

This will dynamically add "AND id IN..." clauses to your query, thereby narrowing down the results.

MySQL: Select Record If Every Search Criteria Is Found in One of the Child Records

2 Answers2