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

所以我有以下query对于mysqldb

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。
为什么第三个查询不返回前两个是什么？我该怎么修？

最佳答案

也许你需要一个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')

关于mysql - 从联接查询中获得意外结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16878413/