MySQL Multiple Search Criteria returns no results but works if removing the last line

I have this Query on a Mysql DB :-

SELECT DISTINCT model.model_id, model.model_ref, 
model.manufact_year, model.discontinued_year, model.mini_spec, 
model.equiv_model_id
FROM model 
LEFT JOIN model_custom_field 
ON model_custom_开发者_Python百科field.model_id = model.model_id
WHERE model.category_id = 140 AND model.manufacturer_id = 1190
AND (model_custom_field.custom_detail_id = 1070 AND model_custom_field.custom_detail_val = '15.6')
AND (model_custom_field.custom_detail_id = 1010 AND model_custom_field.custom_detail_val = 'Dedicated') 

Which returns zero results although there is valid data for the search.

If I remove the last line it works.

Where I have the brackets on the last two lines it is because custom_detail_id and custom_detail_val are linked.

Can someone please tell me if the format of the stat ement is correct.

I Know the last two statements are a contradiction and can't possibly work but I need to know if there is a way round combining the last two maybe in a union.

I understand, your model_custom_field table contains a list of various extended attributes of model that are implemented using EAV model (no pun intended). And you want to find models that have two specific attributes with specific values.

Assuming a single model_id cannot have duplicates of combinations of custom_detail_id and custom_detail_val in model_custom_field:

SELECT
  m.model_id,
  m.model_ref,
  m.manufact_year,
  m.discontinued_year,
  m.mini_spec,
  m.equiv_model_id
FROM model m
  INNER JOIN (
    SELECT model_id
    FROM model_custom_field
    WHERE (custom_detail_id = 1070 AND custom_detail_val = '15.6')
       OR (custom_detail_id = 1010 AND custom_detail_val = 'Dedicated')
    GROUP BY model_id
    HAVING COUNT(*) = 2
  ) f ON m.model_id = f.model_id
WHERE m.category_id = 140 
  AND m.manufacturer_id = 1190

Alternative solution:

SELECT
  m.model_id,
  m.model_ref,
  m.manufact_year,
  m.discontinued_year,
  m.mini_spec,
  m.equiv_model_id
FROM model m
  INNER JOIN model_custom_field f1070 ON m.model_id = f1070.model_id
  INNER JOIN model_custom_field f1010 ON m.model_id = f1010.model_id
WHERE m.category_id = 140 
  AND m.manufacturer_id = 1190
  AND (f1070.custom_detail_id = 1070 AND f1070.custom_detail_val = '15.6')
  AND (f1010.custom_detail_id = 1010 AND f1010.custom_detail_val = 'Dedicated')

If I understand correctly what you are trying to do, than try this:

SELECT DISTINCT model.model_id, model.model_ref,  model.manufact_year, model.discontinued_year, model.mini_spec,  model.equiv_model_id 
FROM model  
LEFT JOIN model_custom_field  ON model_custom_field.model_id = model.model_id 
WHERE model.category_id = 140 
AND model.manufacturer_id = 1190 
AND ((model_custom_field.custom_detail_id = 1070 AND model_custom_field.custom_detail_val = '15.6') 
or (model_custom_field.custom_detail_id = 1010 AND model_custom_field.custom_detail_val = 'Dedicated'))