从多个表中提取不同的行数

Question

我有个简短的问题。我正在寻找这背后的概念，不一定是现成的代码，但如果它不太复杂，我不介意一个快速的解决方案。我有一个管理帐户的应用程序。例如，这些账户就是机场。对于每个机场，我想显示相关的跑道(可能是1，可能是10)和相关的联系人(可能是1，可能是10)。帐户存储在“机场”表中，联系人存储在“联系人”表中，“跑道”存储在“跑道”表中。结构如下：

airports (very fictional data)
+----+--------------+--------------+--------------+-------------+-------------+
| id | airport_name | airport_iata | airport_icao | airport_lat | airport_lon |
+----+--------------+--------------+--------------+-------------+-------------+
|  1 | Schiphol     | SCH          | EHSC         | 123"456'    | 456"789'    |
|  2 | Heathrow     | HEA          | EHHE         | 123"456'    | 456"789'    |
|  3 | John Kennedy | JOH          | EAJK         | 123"456'    | 456"789'    |
+----+--------------+--------------+--------------+-------------+-------------+

contacts (very fictional data)
+----+------------+------------+-----------+------------------+--------------+
| id | airport_id | first_name | last_name |  email_address   | phone_number |
+----+------------+------------+-----------+------------------+--------------+
|  1 |          1 | John       | Ruppert   | john@airport.com |    123456789 |
|  2 |          1 | Mark       | Griffin   | mark@airport.com |    123456789 |
|  3 |          2 | Lia        | Isaac     | lia@gh.nl        |    123456789 |
+----+------------+------------+-----------+------------------+--------------+

runways (very fictional data)
+----+------------+------------+--------+----------+----------------+
| id | airport_id | designator | length | surface  | landing_system |
+----+------------+------------+--------+----------+----------------+
|  1 |          1 | 28R        | 4544m  | tarmac   | ILS (CAT-1)    |
|  2 |          1 | 12L        | 3288m  | concrete | ILS(CAT-3)     |
|  3 |          1 | 14R        | 2644m  | tarmac   | VASI           |
+----+------------+------------+--------+----------+----------------+

我正在寻找的是一个查询(或多个查询)，它将给我一个行和一个机场开始。我在一个像airports.php?id=1这样的页面上，所以我想看看Schiphol的细节。这个机场有2个联系人，所以我也希望能够使用foreach (PHP)循环两个联系人，并列出联系人。我还希望能够显示该机场的3条跑道，并使用foreach访问每一列。

我试过这个：

SELECT airports.*, contacts.*, runways.*
FROM airports
LEFT JOIN contacts ON airports.id = contacts.airport_id
LEFT JOIN runways ON airports.id = runways.airport_id
WHERE airports.id='$id';

我的第一个问题是，它以某种方式返回任意表中的最大行数的两倍。例如，对于id为1的机场，runways有大多数行(3)，因此我将得到6行(6次机场，3次2次接触，2次3次跑道)。第二个问题是，通过使用join (任何形式)，我将得到一个6行的表(正确的行数也不会有太大帮助)，而且我无法循环查看结果，因为每一行都将包含所有机场详细信息、所有联系人详细信息和所有跑道。

我可以不使用多条语句而得到一个包含1个元素的数组，一个包含2个联系人元素的数组，以及一个包含3个元素的数组吗？有人告诉我加入是要走的路，但现在感觉并非如此。我做错了什么？

耽误您时间，实在对不起!

Answer 1

你的JOIN表现得很正常。

如果您有一个airports行，而没有匹配的contacts或runways，您将得到如下内容：

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol |   NULL  |  NULL

如果你有一个机场，一个联系人，一个跑道，你会得到：

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R

然而，当您有多个匹配的contacts和runways，并且一个接触点和一个跑道之间没有联系时，那么每个接触点都会与每条跑道成对，正如您已经看到的：

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R
 Schiphol | Ruppert |  12L
 Schiphol | Ruppert |  14R
 Schiphol | Griffin |  28R
 Schiphol | Griffin |  12L
 Schiphol | Griffin |  14R

这就是所谓的交叉产品。你得到了你想要的两倍的行，因为你要用3行横穿2行(2 *3= 6)。如果你有7个隐形眼镜和5个跑道，你会得到35排。

您最好的解决方案是什么将取决于您是DBMS的能力。

最坏的情况是，运行三个查询:一个查询airport结果，一个查询contacts结果，另一个查询runways结果。将所有三个结果集都拉到PHP中。如果要返回多个机场的结果，则需要遍历airports结果，并(在该循环中)通过其他两个结果集循环，以提取当前机场的联系人和跑道。

许多DBMSes提供的函数允许您将每个机场的联系人或跑道的行组合成XML或JSON。在这种情况下，您将有一个主查询，但在其中有两个子查询来为其他表生成两个对象。类似于(仅限伪代码！)：

SELECT airport_name, airport_iata, airport_icao
      ,(SELECT <XML/JSON func>(first_name, last_name, email_address, phone_number)
          FROM contacts
         WHERE airport_id = airports.id
       ) as contacts_obj
      ,(SELECT <XML/JSON func>(designator, length, surface, landing_system)
          FROM runways
         WHERE airport_id = airports.id
       ) as runways_obj
  FROM airports
 WHERE airports.id = 1;

同样，只使用伪代码；您需要弄清楚DBMS函数的细节。

除了显式的XML或JSON函数之外，大多数DBMSes都能够创建字符串聚合。这将与上面的函数非常相似，除非是手动的；您可能会为每一行(first_name + '|' + last_name + '|' + ...)构建一个字符串，并使用聚合函数将多个联系人组合成一个字符串(当然，使用不同的分隔符)。