Mysql: Get all results that has all this relations

I have two tables:

  objects                    object_features
-------------              -------------------
id                         id
name                       object_id
                           term_id

What I want to achieve is, giving a list of features, get all objects that ha开发者_JS百科s all of them. I'm trying this:

SELECT objects.* 
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')

This is the php code I'm using:

$feature_list = array(1,3,4,10);
$sql = 'SELECT objects.* 
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('.implode(',', $feature_list).')';

This is near to what I need, but differing that it returns me any object that has any of the features given, instead of ALL the features

one option is to group by the data you want returned from object and add a having clause that counts object.id and tests to see if it is the same as the length of the array.

SELECT objects.id, objects.name 
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')
group by objects.id,objects.name
having count(objects.id) = 4

Cant swear to the syntax on that as I've been writing tsql recently and don't have an instance of mysql to test on.

try

'WHERE term_id = '.impode(' AND termid = ', $features_ids).')'

WHERE termid = 1 AND termid = 3 AND termid = 5

Actually you need a GROUP BY to group by each object and using a HAVING clause to allow only rows that have all the termids

SELECT objects.* 
FROM `object_features` LEFT JOIN `objects` ON ( objects.id=object_features.object_id)
WHERE term_id IN ('1','3','4','10')
GROUP BY objects.id, objects.name
HAVING count(term_id) = 4

The SQL way of doing it would be:

SELECT objects.* 
  FROM objects
 WHERE null not in 
   ( 
      select of.object_id
        from features f 
        left join object_features of on (f.id = of.id)
   )

Assuming you have a features table with all the features.

If you need to list only certain features, you can do (check out the where condition on the subquery):

SELECT objects.* 
  FROM objects
 WHERE null not in 
   ( 
      select of.object_id
        from features f 
        left join object_features of on (f.id = of.id)
       where f.id in (1,2,3,4,5)
   )