如何比较由WHERE子句定义的同一表中的字段 - How to Compare Fields in same table defined by WHERE clause

How to Compare Fields in same table defined by WHERE clause

我试图比较2个不同制造商之间的产品表中的product_models。我发现相同的产品有2个不同的product_model号码。相似但又不同。例如，制造商1 (ELD)的CNELD-1004与制造商2 (MC)的1004是相同的产品。我试图显示来自制造商2的产品，它不像来自制造商1的相同product_model

不使用php，有办法做到这一点mysql?

select products_model  AS MCProducts from products where manufacturers_id = 2; 
select products_model  AS ELDProducts from products where manufacturers_id = 1; 
Select  MCProducts from products WHERE MCProducts  not LIKE  "%ELDProducts%"

select products_model, CASE

  WHEN manufacturers_id = 2 THEN 'MCProducts' 
  WHEN manufacturers_id = 2 THEN 'ELDProducts'
  END 
from products where WHERE MCProducts  not LIKE  "%ELDProducts%"

我想这也可以。

select products_model  AS MCProducts from products where manufacturers_id = 2 and     products_model not in (select products_model  from products where manufacturers_id = 1);