MySQL自我引用加入

Question

我有两个表：

Customers (
    int(11) Id, 
    varchar(255) Name, 
    int(11) Referred_ID -- Referred_ID being reference to an Id field 
                        -- (no key on that field) and 
)

另一个表是：

Invoices (
    int(11) Id, 
    date Billing_date, 
    int(11) Customer_ID
)

我想选择发票的Id，Billing_date，最重要的是，客户的Name此客户是指。

现在，我只能通过这样的查询选择他的推荐人的ID：

SELECT Invoices.Id, Invoices.Billing_date, Customers.Name, Referred_ID
    FROM Invoices 
    INNER JOIN Customers ON Invoices.Customer_Id = Customers.Id;

我应该如何修改我的查询以用其所有者名称替换该referred_id？

顺便说一句，这是来自2015年类似的mysql。

Answer 1

您可以使用两次使用别名来加入推荐

    SELECT Invoices.Id, Invoices.Billing_date, Customers.Name, Referred.Name 
    FROM Invoices 
    INNER JOIN Customers ON Invoices.Customer_Id = Customers.Id
    INNER JOIN Customers Referred on Referred.id =  Customers.Referred_ID;

Answer 2

在JOIN

中两次使用客户表

 SELECT Invoices.Id, Invoices.Billing_date,
 c1.Name as customername,
c1.Referred_ID,
c2.Name as refername 
FROM Invoices INNER JOIN Customers c1 ON Invoices.Customer_Id = c1.Id
join Customers c2 on  c1.Id=c2.Referred_ID