How to structure this query...?

Question

Hi Everyone...

Consider the following table....

hotel     facilities
1       internet
1       swimming pool
1       wi-fi
1       parking
2       swimming pool
2       sauna
2       parking
3       toilets
3       bungee-jumping
3       internet
4       parking
4       swimming pool

I need to select only the hotels that have parking, swimming pool and internet....?

I worked out the following....

SELECT hotel
FROM   table
WHERE  facilties IN(internet, swimming pool, parking)

This query selects the hotels that has atleast one among the choices.

But what i need is a query that selects the hotels that has ALL of the selected facilities...

Thanks for your suggestions....

Edit : I should also mention that the number of choices are given by the user and the query should be built dynamically at runtime...

Answer 1

SELECT first.hotel
FROM table AS first
INNER JOIN table AS second
  ON first.hotel=second.hotel
INNER JOIN table AS third
  ON second.hotel=third.hotel
WHERE first.facilities='internet'
  AND second.facilities='swimming pool'
  AND third.facilities='parking'

Answer 2

You could use a GROUP BY and HAVING:

SELECT hotel
FROM table
WHERE facilties IN('internet', 'swimming pool', 'parking')
GROUP BY hotel
HAVING SUM(facilities = 'internet') AND SUM(facilities = 'swimming pool') AND SUM(facilities = 'parking')

Answer 3

select t1.hotel
from tab t1,tab t2, tab,t3
where t1.hotel = t2.hotel and t2.hotel = t3.hotel
and t1.facilities = 'internet' and t2.facilities = 'parking'
and t3.facilities = 'swimming pool'

Answer 4

There are three ways of doing this:

Joins

SELECT hotel
FROM table t1
JOIN table t2 ON t1.hotel = t2.hotel AND t2.facilities = 'swimming pool'
JOIN table t3 ON t1.hotel = t3.hotel AND t3.facilities = 'parking'
WHERE t1.facilities = 'internet'

Aggregation

SELECT hotel
FROM table
GROUP BY hotel
WHERE facilities IN ('internet', 'swimming pool', 'parking')
HAVING COUNT(1) = 3

Note: this assumes no duplicates of (hotel,facilities)

EXISTS

SELECT hotel
FROM table t
WHERE facilities = 'internet'
WHERE EXISTS (SELECT 1 FROM table WHERE hotel = t.hotel AND facilities = 'swimming pool')
AND EXISTS (SELECT 1 FROM table WHERE hotel = t.hotel AND facilities = 'parking')

A good SQL optimizer will probably optimize them all to be the same but I've found MySQL can be somewhat unpredictable in this department so you'll need to benchmark them with meaningful datasets (>1 million rows) to determine which is best.

See Oracle vs MySQL vs SQL Server: Aggregation vs Joins as an example of some of the differences.

Answer 5

Another approach.

If you want to build the query dynamically:

Select Distinct hotel from facilities where 1=1
<logic here>
and facilities='internet'
<logic here>
and facilities='pool'
<logic here>
......

Execute your query