SQL Query Help - Is subquery needed?

Hello

I have a table called 'resorts' with details of hotels. There is a table called features_list which holds details of features at resorts. Finally there is a table which links the two together called featuers_resorts (has resort id and feature id).

Basically the idea is that on the frontend a user can click what features they would like from a list and then the site queries this database to fetch results that include ALL of the selected.

If you look at this query you will see the problem:

SELECT * FROM resorts 
INNER JOIN resorts_features 
ON resorts.id = resorts_features.resort_id 
WHERE (resorts.star_rating = '4') 
[B]AND resorts_features.feature_id = '11'
AND resorts_features.feature_id = '21' 
AND resorts_features.feature_id = '14' 
AND resorts_features.feature_id = '23' 
AND resorts_features.feature_id = '7'[/B]

Code (markup):

The part that I have made bold is the problem and obviously won't return results as expected. How can I write this? I can only think of a subquery but a little lost?

Thanks for your help

 

I'm not really sure this is a good way and if it will work, but the first thing came to my mind is something like:

SELECT * FROM resorts 
WHERE (resorts.star_rating = '4') AND 
           (SELECT count(id)
            FROM resorts_features as a  
            WHERE  resorts.id = resorts_features.resort_id AND
                       resorts_features.feature_id in ('11', '21', '14')) = 3 --here goes --feature count

PHP:

and i didn't check syntax

 

Your WHERE clause is incorrect. You are essentially asking for hotels who's feature_id field contains 5 different values--which is impossible, it can only hold 1 value. You want to replace the ANDs between those with ORs like this:

WHERE (resorts.star_rating = '4') AND
(resorts_features.feature_id = '11' OR resorts_features.feature_id = '21'  OR resorts_features.feature_id = '14' OR resorts_features.feature_id = '23' OR resorts_features.feature_id = '7') 

PHP:

That way instead of the feature_id having to be all 5 values, it has to only be one of those 5 to show.

 

oh yeah people definetly will be looking for hotels that have swimming pool OR tennis court OR air conditioning

 

seoelk.com is right--my previous post is wrong--don't use it. I thought you were looking up specific hotels, not specific features.

 

Hi everyone & thanks for the replies.

Indeed plog - so you see my dilemma

Thanks for that example soelk, will have a go... any chance you can explain the 'here goes feature count' bit?

 

Following should help..

SELECT col1, col2, col3, [..col-N] FROM resorts A
JOIN 
(SELECT DISTINCT resort_id FROM resorts_features WHERE feature_id IN(11, 21, 14, 23, 7)) B
ON A.resort_id = B.resort_id
WHERE A.star_rating = 4

Code (markup):

I've assumed that feature_id & star_rating columns are numeric and hence have removed quotes from around values.
Also, added DISTINCT in sub-query to avoid multiple entries of same resort in final result.

 

Thanks mastermunj

I ran it through SQL on mysql and got the following:

[B]Error

SQL query: Documentation[/B]

SELECT *
FROM resorts
JOIN (

SELECT DISTINCT resort_id
FROM resorts_features
WHERE feature_id
IN ( 11, 21, 14, 23, 7 )
) ON resorts.resort_id = resorts_features.resort_id
WHERE resorts.star_rating =4
LIMIT 0 , 30

[B][COLOR="red"]MySQL said: Documentation
#1248 - Every derived table must have its own alias[/COLOR][/B] 

Code (markup):

 

It seems you edited the query.
You removed A & B aliases I had used. Either use them, or use other ones you like.
Basically, the inner select query generates a derived table, which must have an alias, and hence the error.
Try running the query I gave as it is and verify if results are as desired or not.

 

SELECT * FROM resorts
INNER JOIN resorts_features
ON resorts.id = resorts_features.resort_id
WHERE ((resorts.star_rating) = '4')
AND ((resorts_features.feature_id) = '11')
AND ((resorts_features.feature_id) = '21')
AND ((resorts_features.feature_id) = '14')
AND ((resorts_features.feature_id) = '23'))
AND ((resorts_features.feature_id) = '7');

 

I thought I would have to edit it slightly.

Here it is with nothing amended:

[B]Error

SQL query: Documentation[/B]

SELECT col1, col2, col3, [..col - N]
FROM resorts A
JOIN (

SELECT DISTINCT resort_id
FROM resorts_features
WHERE feature_id
IN ( 11, 21, 14, 23, 7 )
)B ON A.resort_id = B.resort_id
WHERE A.star_rating =4
LIMIT 0 , 30

[B][COLOR="red"]MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ . . col - N ] FROM resorts A  JOIN  ( SELECT DISTINCT resort_id FROM resorts_f' at line 1 [/COLOR][/B]

Code (markup):

And here is it with the col1,col2,col3 changed to just name (one of the column names):

[B]Error

SQL query: Documentation[/B]

SELECT name
FROM resorts A
JOIN (

SELECT DISTINCT resort_id
FROM resorts_features
WHERE feature_id
IN ( 11, 21, 14, 23, 7 )
)B ON A.resort_id = B.resort_id
WHERE A.star_rating =4
LIMIT 0 , 30

[B][COLOR="red"]MySQL said: Documentation
#1054 - Unknown column 'A.resort_id' in 'on clause' [/COLOR][/B]

Code (markup):

 

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND ( ( resorts_features . feature_id ) = '7' )
LIMIT 0, 30' at line 1

 

I sorted the syntax but it returned 0 results - this is the same as my query, you have just put brackets around bits which hasn't made a difference ;-)

 

SOLVED!!

Thanks to mastermunj

It was just the column was resorts.id not resorts.resort_id.

Looks like its working as intended....you legend!

Thanks again

 

I'm glad I could help.

I wrote col1, col2, col3 because we must avoid using * as much as possible.