mysql - 在 MYSQL 中连接多个表时出现错误(

Question

我有三张 table

phoneRecord - ID,phone
companies - ID,phone1,phone2, name
contacts - ID, phone1, phone2, companies.ID

我想将表格电话记录连接到公司和联系人，我可以通过以下方式这样做:

SELECT 
    phoneRecord.*, 
    companies.ID, 
    contacts.ID, 
FROM phoneRecord
    LEFT JOIN contacts
    ON phoneRecord.Phone=contacts.Phone1 OR phoneRecord.Phone=contacts.Phone2
    LEFT JOIN companies ON phoneRecord.Phone=companies.phone1 OR phoneRecord.phone=companies.phone2 

但是，我还需要一件事。当返回联系人时，我想知道联系人所在的公司(如果 contact.companies 与 company.id 匹配)。可以这样做吗？

我尝试过类似的方法:

 SELECT 
    phoneRecord.*, 
    companies.ID, 
    companies.name, 
    contacts.ID, 
FROM phoneRecord
    LEFT JOIN contacts
        LEFT JOIN companies ON contacts.company=companies.ID
    ON phoneRecord.Phone=contacts.Phone1 OR phoneRecord.Phone=contacts.Phone2
    LEFT JOIN companies ON phoneRecord.Phone=companies.phone1 OR phoneRecord.phone=companies.phone2 

但它给了我一个错误代码 1066:不是唯一的表/别名:'公司'。

我已经按照托马斯的建议切换了代码，但它给了我同样的错误。也许我误解了他的意思。代码如下:

SELECT
    phoneRecord.*, 
    companies.ID, 
    companies.name, 
    contacts.ID, 
FROM phoneRecord
    LEFT JOIN contacts ON phoneRecord.Phone=contacts.Phone1 OR phoneRecord.Phone=contacts.Phone2
    LEFT JOIN companies ON contacts.company=companies.ID
    LEFT JOIN companies ON phoneRecord.Phone=companies.phone1 OR phoneRecord.phone=companies.phone2 

我终于让它工作了:

SELECT 
    phoneRecordID,
    phoneRecordPhone,
    companyID,
    contactsID,
    contactsCompany,
    companies.name
    FROM(
SELECT 
    phoneRecord.ID as phoneRecordID, 
    phoneRecord.Phone as phoneRecordPhone,
    companies.ID as companyID, 
    contacts.ID as contactsID, 
    contacts.company as contactsCompany
FROM phoneRecord
    LEFT JOIN contacts
    ON phoneRecord.Phone=contacts.Phone1 OR    phoneRecord.Phone=contacts.Phone2
    LEFT JOIN companies ON phoneRecord.Phone=companies.phone1 OR phoneRecord.phone=companies.phone2) as result
LEFT JOIN companies ON contactsCompany = companies.id

谢谢，这非常有帮助。

Answer 1

我在结果中检索列表，然后使用左连接与联系人，希望这对您有用

   SELECT cmid from( SELECT 
   phoneRecord.*, 
   companies.ID as cmid, 
  contacts.ID, 
  FROM phoneRecord
  LEFT JOIN contacts
  ON phoneRecord.Phone=contacts.Phone1 OR phoneRecord.Phone=contacts.Phone2
   LEFT JOIN companies ON phoneRecord.Phone=companies.phone1 OR phoneRecord.phone=companies.phone2) as result 
    left join contacts on contacts.ID = result.cmid