How to get the correct checkbox result from database MySQL?

I have 3 table here.

1)Hotel

-----------------
|Hotel_ID | Name     |
-----------------
|    1    |Shangrila |
----------------------
|    2    |GoldHill  |
----------------------
|    3    |BayBeach  |
----------------------

2)Feature

----------------------
|Feature_ID| Feature |
----------------------
|     1    |  Goft   |
----------------------
|     2    |Internet |
----------------------

3)Brdige_Hotel_Feature

-------开发者_如何学编程-----------------
|Hotel_ID | Feature_ID |
------------------------
|    1    |      1     |
------------------------
|    1    |      2     |
-----------------------
|     2   |      1     |
-----------------------

It mean each hotel might have more than 1 feature.

My idea is like this , let say, if i want get the result from table 3 Bridge_Hotel_Feature. If the Feature_ID = 1 , i get Hotel 1 and 2. **If the Feature_ID = 1 , 2. I just want to get Hotel 1. But i always get the both Hotel_ID 1 and 2. ** Please help me the solution to get only the feature match with the Hotel_ID.

Below is the code i try.

SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge1_Hotel_Features b, Features f
where 0=0
AND b.Feature_ID = f.Feature_ID 
AND b.Hotel_ID = r.Hotel_ID

<cfif #FORM.Feature_ID# IS NOT "">
    AND f.Feature_ID IN (#FORM.Feature_ID#)
</cfif>

If Feature_ID = 1 ,2 , the result must only get Hotel_ID = 1

Because only Hotel_ID = 1 has both features, correct? Assuming #FORM.Feature_ID# does not contain duplicates, use a HAVING clause to dynamically identify hotels with all of the requested features.

SELECT Hotel_ID, COUNT(Feature_ID) AS FeatureCount
FROM   Bridge_Hotel_Feature
<!--- find matching features --->
WHERE  Feature_ID IN ( <cfqueryparam value="#FORM.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
GROUP BY Hotel_ID
<!--- having ALL of the requested features --->
HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(FORM.Feature_ID)#" cfsqltype="cf_sql_integer">

You could then join to it as a derived table or possibly a subquery. The sql needs optimization, but conceptually something like

SELECT h.Hotel_ID, h.Name, f.Feature
FROM   Hotel h
      INNER JOIN Bridge_Hotel_Feature b ON b.Hotel_ID = h.Hotel_ID
      INNER JOIN Features f ON b.Feature_ID = f.Feature_ID
      INNER JOIN
      (
        SELECT Hotel_ID, COUNT(Feature_ID) AS FeatureCount
        FROM   Bridge_Hotel_Feature
        <!--- find matching features --->
        WHERE  Feature_ID IN ( <cfqueryparam value="#FORM.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
        GROUP BY Hotel_ID
        <!--- having ALL of the requested features --->
        HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(FORM.Feature_ID)#" cfsqltype="cf_sql_integer">
    ) ck ON ck.Hotel_ID = h.Hotel_Id

Change sql to:

SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge1_Hotel_Features b, Features f
where b.Feature_ID = f.Feature_ID 
      AND b.Hotel_ID = h.Hotel_ID

<cfif #FORM.Feature_ID# IS NOT "">
    AND b.Feature_ID IN (#FORM.Feature_ID#)
</cfif>

Essentially, the optional part of where clause should be restricting the feature_id in the Bridge_Hotel_Features table.

You need to use EXIST to solve this problem.

SELECT h.Name , h.Hotel_ID, f.feature
FROM Hotel h, Bridge_Hotel_Feature b, Feature f
where b.Feature_ID = f.Feature_ID 
      AND b.Hotel_ID = h.Hotel_ID
AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 1 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)
AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 2 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)

EDIT: The above is an example of what your query needs to look like. To make it dynamic you will add a loop.

I don't have coldfusion experience myself so I can't tell you verbatim what to do with that code.

But what you need to do is wrap the following section of code in a loop and append this to your query string for each checkbox and replace the feature_id in the where clause with the feature_id of each checkbox. AND EXISTS (SELECT feature_id FROM bridge_hotel_feature WHERE feature_id = 1 AND bridge_hotel_feature.hotel_ID = h.hotel_ID)

I hope this makes it clearer for you.