require all values in array to be matched when using where_in

[eluser]Andy UK[/eluser]
Thanks for the reply CroNiX,

I see where you're going with the proposed solution, although i'm not sure how to implement it either. Since I can build the query with php, is there any reason i can't do the foreach loop i mentioned before? It's not working when the the filter array has more than one value in it though...

Code:

// Features Filter
  if(is_array($this->session->userdata('selected_features')) && count($this->session->userdata('selected_features')) > 0)
  {
   foreach($this->session->userdata('selected_features')) as $feature)
   {
   $this->db->where('feat_prop_join.feature_id', $feature);
   }
  }

Which would translate to multiple where clauses like this (assuming three filter values are chosen):

Code:

->where('feat_prop_join.feature_id', 6)
->where('feat_prop_join.feature_id', 34)
->where('feat_prop_join.feature_id', 23)

I mean it's not working as it is, but could this be a solution with some adjustment? I have a lack of understanding as to how SQL treats many to many relationships, since 'feat_prop_join.feature_id' could be any number of different feature ids. If a property has, say 17 features in the join table, can 'feat_prop_join.feature_id' be any one of those 17 values at once when running the query? If that's the case then i don't understand why having multiple where statements doesn't work.

Using the above example, say a property has features of ids 3, 6, 32, 34, 23, 10. Wouldn't the above code return this property as part of the result set? Again, what's confusing me is the fact that this is a many to many relationship and i've never had to deal with something like this before.

Thanks again