Problem Returning Rows Where...

[Wagtail]

Hello everyone,

 

I'm having a slight difficulty getting a query to work. What I'm trying to do is return hotels that have certain features such as "dry cleaning" etc.

 

This query (pseudo code) doesn't work. It returns a message saying: MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0009 sec )

SELECT ... WHERE... f.features_name='dry cleaning' && f.features_name='spa' 

This query returns hotels that have "dry cleaning" and/or "spa". What must I do so only hotels are returned that have both features?

SELECT...WHERE ...f.features_name IN ('dry cleaning', 'spa')

Thank you in advance.

[HartleySan]

I can't imagine your query will ever work because you should never have a single rows in your table where the features_name is set to both "dry cleaning" and "spa". That makes no sense.

Try changing the && to ||, and you should be fine.

[Wagtail]

Hey HartleySan,

 

thank you for your prompt reply. I'm using the intermediary table which contains the hotel_id and the feaures_id, so each hotel will have multiple features. I tried the OR but it returns hotels that have only one of the 2 example features, but I need the hotels that have both features.

 

 

Will try to get it right.

 

 

Thanks. Hope your Monday is off to a good start.

[HartleySan]

You basically have two choices:

1) Do a self-join on the table.

2) Do an OR on the table, and then use PHP logic to pare down the list.

 

That make sense?

[Wagtail]

Yes, thank you.