I’m using a junction table which holds the columns hotel_ids and features_ids.
In order to filter the hotels according to the chosen features, I’m using a WHERE clause such as WHERE f.features_name=‘room service’. But what must I do if there are multiple features? Must I use AND or OR, such as WHERE f.features_name=‘room service’ OR f.features_name=‘internet access’? The main thing is that only those hotels that have all of the chosen features are returned.
SELECT h.hotel_id
, h.hotel_name
, h.other_hotel_stuff
, GROUP_CONCAT(f.features.name) AS features
FROM ( SELECT hf.hotel_id
FROM features
INNER
JOIN hotel_features
ON hotel_features.features_id = features.features_id
WHERE features.features_name IN
( 'room service'
, 'internet access' ) /* note 2 features listed */
GROUP
BY hotel_features.hotel_id
HAVING COUNT(*) = 2 /* note 2 features must be counted */
) AS x
INNER
JOIN hotels AS h
ON h.hotel_id = x.hotel_id
INNER
JOIN hotel_features AS hf
ON hf.hotel_id = h.hotel_id
INNER
JOIN features AS f
ON f.features_id = hf.features_id
GROUP
BY h.hotel_id
GROUP_CONCAT is, of course, a mysql proprietary function
it’s working. Thank you very much! The query is rather complex so I’m glad I asked. You’ve saved me a few hours looking stupidly at the PC and wondering what to do next.
The query you gave me is working well, thanks. What would I have to change if I wanted to add another intermediary table “hotel_activities” to the mix? I thought I’d ask.
sorry to bother you with this but could you please have a quick look at my query?
I have used the same columns and tables for the second GROUP_CONCAT as I used for the first GROUP_CONCAT, adding a comment here and there. This is just so one can see the structure of the query. Possibly the INNER JOINS need some amending?
I know you said:
you need the new subquery to produce one result per hotel_id, then join that to the query
Thank you!!!
SELECT h.hotel_id
, h.hotel_name
, h.other_hotel_stuff
, GROUP_CONCAT(f.features.name) AS features
FROM ( SELECT hf.hotel_id
FROM features
INNER
JOIN hotel_features
ON hotel_features.features_id = features.features_id
WHERE features.features_name IN
( 'room service'
, 'internet access' )
GROUP
BY hotel_features.hotel_id
HAVING COUNT(*) = 2
) AS x, // added a comma here
GROUP_CONCAT(f.features.name) AS features
FROM ( SELECT hf.hotel_id
FROM features
INNER
JOIN hotel_features
ON hotel_features.features_id = features.features_id
WHERE features.features_name IN
( 'room service'
, 'internet access' )
GROUP
BY hotel_features.hotel_id
HAVING COUNT(*) = 2
) AS xx // added an x here
INNER
JOIN hotels AS h
ON h.hotel_id = x.hotel_id // inner join from first group_concat
INNER
JOIN hotels AS h
ON h.hotel_id = xx.hotel_id // inner join from second group_concat
INNER
JOIN hotel_features AS hf
ON hf.hotel_id = h.hotel_id
INNER
JOIN features AS f
ON f.features_id = hf.features_id
GROUP
BY h.hotel_id
whole bunch of things wrong with your query – you cannot add a column in the middle of the FROM clause, you cannot add a second FROM keyword, and your second subquery is identical to the first, going after ‘room service’ and ‘internet access’ as qualifying features
so i’ve re-thought the query and made a couple of changes
i’ve changed the first subquery to retrieve all features for each hotel, not via the main query, and included code to detect when the features include all the ones that were specified as qualifying
then i’ve added a second subquery, to retrieve all amenities for each qualified hotel
thus the outer query doesn’t need a GROUP BY any more
SELECT h.hotel_id
, h.hotel_name
, h.other_hotel_stuff
, x.features
, xx.amentities
FROM hotels AS h
INNER
JOIN ( SELECT hotel_features.hotel_id
, GROUP_CONCAT(features.features_name) AS features
, SUM(CASE WHEN features.features_name
IN ( 'room service'
, 'internet access' )
THEN 1 ELSE NULL END ) AS qualifying
FROM hotel_features
INNER
JOIN features
ON features.features_id = hotel_features.features_id
GROUP
BY hotel_features.hotel_id
) AS x
ON x.hotel_id = h.hotel_id
AND x.qualifying = 2
INNER
JOIN ( SELECT hotel_amenities.hotel_id
, GROUP_CONCAT(amenities.amenities_name) AS amenities
FROM hotel_amenities
INNER
JOIN amenities
ON amenities.amenities_id = hotel_amenities.amenities_id
GROUP
BY hotel_amenities.hotel_id
) AS xx
ON xx.hotel_id = h.hotel_id
I have to ask because I haven’t tried the code yet, but this is intended to work with 2 intermediate tables? The one table I have is for amenities and the other table for activities - each hotel can have multiple amenities and multiple activities.
Aren’t features the same as amenities? I definitely have an amenities/features table and now I tried adding another intermediate table which I arbitrarily called “activities”.
I am happy to report that your code does work! Thank you for helping me with this. That’s one problem solved, another 1000 to go… It seems that solving one problem results in another problem… it’s a vicious cycle.
