MySQL：涉及两个表和第三个关联表的复合连接语句

Question

我有两个为两个不同系统构建的表。我有一个代表顾客的表格（称为“销售线索”）的记录，并且在另一个表格（称为“经理”）中记录有完全相同的客户，但“经理”使用不同的字段记录

（例如， “包含一个电子邮件地址，而”经理“包含两个不同电子邮件的字段 - 其中任何一个都可能是来自”潜在客户“的电子邮件）。

所以，我创建了一个包含lead_id和经理标识的相关表格。目前这个关联表是空的。

我试图查询“潜在客户”表给我的记录匹配“管理”电子邮件字段与单个“主角”电子邮件字段，同时忽略已添加到“相关”表。 （这样我可以看到有多少匹配尚未被相关的是线索。）这是我的电流，无效的SQL尝试：

SELECT leads.id, manager.id 
    FROM leads, manager 
    LEFT OUTER JOIN correlation ON correlation.lead_id = leads.id 
    WHERE correlation.id IS NULL 
    AND leads.project != "someproject" 
    AND (manager.orig_email = leads.email OR manager.dest_email = leads.email) 
    AND leads.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-10 23:59:59' 
    ORDER BY leads.created ASC; 

我得到的错误：未知列在“leads.id”“的条款”

你不知道之前：有在“引线”表中的记录，其中leads.project =“someproject”和leads.created落在那些日期间！我已经包含了这些附加参数的完整性。

Answer 1

你有表中列出的南辕北辙所以LEFT OUTER JOIN加盟manager到correlation，而不是leads到correlation，你打算。试着轮交换他们：

SELECT leads.id, manager.id 
    FROM manager, leads 
    LEFT OUTER JOIN correlation ON correlation.lead_id = leads.id 
    WHERE correlation.id IS NULL 
    AND leads.project != "someproject" 
    AND (manager.orig_email = leads.email OR manager.dest_email = leads.email) 
    AND leads.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-10 23:59:59' 
    ORDER BY leads.created ASC; 

一般我会建议不要混合新旧风格的加入，以避免正是这种类型的错误。我更愿意看到这样的事情：

SELECT leads.id, manager.id 
    FROM leads 
    LEFT OUTER JOIN correlation ON correlation.lead_id = leads.id 
    JOIN manager ON manager.orig_email = leads.email OR manager.dest_email = leads.email 
    WHERE correlation.id IS NULL 
    AND leads.project != "someproject" 
    AND leads.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-10 23:59:59' 
    ORDER BY leads.created ASC;