mysql - 多表连接

Question

我有一个问题，我只需要从我的初始表中获取一些东西，然后从另一个表中加入另一个项目，然后从另一个表中加入另一个项目。我认为这很容易，但远非如此。这是我的 SQL 信息(为简单起见，将其保存在一条记录中)。

select * from customers limit 1 \G;
*************************** 1. row ***************************
           CustomerID: 9
       CustomerMapsco: 459
       CustomerActive: 1
    CustomerFirstName: John
     CustomerLastName: Doe
CustomerServiceStreet: 1314 Road Rd.
  CustomerServiceCity: City
 CustomerServiceState: TX
   CustomerServiceZip: 12345
CustomerBillingStreet: 1314 Road Rd.
  CustomerBillingCity: City
 CustomerBillingState: TX
   CustomerBillingZip: 12345
    CustomerHomePhone: 1231231234
    CustomerCellPhone: 1231231234
    CustomerWorkPhone: 1231231234
      CustomerWorkExt: 12345
          CustomerFax: 1231231324
        CustomerEmail: email@tld.COM
          CustomerDog: 0
               CrewID: 1
           ScheduleID: 1
            protected: 1


mysql> select * from customerservice limit 1 \G;
*************************** 1. row ***************************
       CustomerSvcID: 15
          CustomerID: 9
       ServiceTypeID: 1
     FrequencyTypeID: 1
               DayID: 5
     CustomerSvcCost: 21
CustomerSvcBeginDate: 2007-01-01 00:00:00
  CustomerSvcEndDate: NULL
1 row in set (0.00 sec)

mysql> select * from frequency
    -> ;
+-----------------+---------------+
| FrequencyTypeID | FrequencyType |
+-----------------+---------------+
|               1 | Weekly        |
|               2 | Biweekly      |
|               3 | One Time      |
+-----------------+---------------+

我需要的是以下列:

customers.CustomerID, customers.CustomerActive, customers.protected, frequency.FrequencyType

我想我必须进行三重连接才能从 customerservice 表中获取 FrequencyType，因为它们只在该表中引用，而不是在 customer 表中引用。因此，我必须采取额外的步骤来获取该信息 ( customers <> customerservice <> frequency )。

Answer 1

你是对的，你需要连接所有三个表来获取该信息，这应该足够了:

SELECT c.CustomerID, c.CustomerActive, c.protected, f.FrequencyType
FROM customers c
   JOIN customerservice s
      ON c.CustomerID = s.CustomerID
   JOIN frequency f
      ON s.FrequencyTypeID = f.FrequencyTypeID