mysql - 从连接查询中获得意外结果

Question

所以我有以下 query 用于 mysql db

SELECT CFM.display_name, CFM.field_type, CFM.option_value
FROM company_mast
LEFT JOIN component_mast ON company_mast.id = component_mast.company_id
LEFT JOIN CustomField_mast CFM ON CFM.Company_ID = Component_mast.Company_ID
AND CFM.Component_ID = component_Mast.Component_ID
WHERE component_mast.component_name =  'Assets'
AND component_mast.project_id =  '1'

它返回的正是我想要的。我也可以这样查询

SELECT CFM.display_name, CFM.field_type, CFM.option_value
FROM company_mast
LEFT JOIN component_mast ON company_mast.id = component_mast.company_id
LEFT JOIN CustomField_mast CFM ON CFM.Company_ID = Component_mast.Company_ID
AND CFM.Component_ID = component_Mast.Component_ID
WHERE component_mast.component_name =  'Assets'
AND company_mast.company_name =  'Company' <!-- this line is different -->

它再次准确返回我想要的。然而，如果我运行这个查询

SELECT CFM.display_name, CFM.field_type, CFM.option_value
FROM company_mast
LEFT JOIN component_mast ON company_mast.id = component_mast.company_id
LEFT JOIN CustomField_mast CFM ON CFM.Company_ID = Component_mast.Company_ID
AND CFM.Component_ID = component_Mast.Component_ID
WHERE component_mast.component_name =  'Assets'
AND component_mast.project_id =  '1'
AND company_mast.company_name =  'Company'

我得到 0 个结果。目前，这是可行的，但随着该数据库的增长，同一公司中将出现多个同名组件。所以我需要添加一个额外的唯一标识符，即project_id。

为什么第三个查询不返回前两个查询？我该如何解决？

Answer 1

也许您需要 or 条件而不是 and 条件:

SELECT CFM.display_name, CFM.field_type, CFM.option_value
FROM company_mast
LEFT JOIN component_mast ON company_mast.id = component_mast.company_id
LEFT JOIN CustomField_mast CFM ON CFM.Company_ID = Component_mast.Company_ID
AND CFM.Component_ID = component_Mast.Component_ID
WHERE component_mast.component_name =  'Assets'
AND (component_mast.project_id =  '1' or company_mast.company_name =  'Company')