在连接中具有2个字段的MySQL连接

Question

我正在为一家飞行学校创建一个预订数据库，其中包含时间和飞行员的表，作为当天航班的可视化日程计划类型表示。我让它工作得很好，直到他们得到了一架有时需要副驾驶的飞机。

他们的花名册上有一些永久的飞行员(他们希望一直在计划表上看到)，还有一些偶尔会用到(他们只想在当天有预订的时候出现在那里)。他们还希望能够在计划器上设置自己的飞行员顺序，因此我在员工记录中创建了一个int_LISTORDER字段来设置列表中的顺序，只有在为他们进行预订时，int_LISTORDER为500及以上的人才会出现。为了将副驾驶员添加到组合中，booking除了用于Pilot- in -Command的txt_STAFFNAME之外，还添加了一个txt_STAFFNAME2字段。

我对原始的pilot only版本进行了以下查询，以选择在特定日期预订的所有飞行员( $tl_date变量)，这些飞行员与员工列表中的飞行员列表匹配，并获得他们的列表顺序，并将其与列表顺序低于500的飞行员列表合并：

        (SELECT DISTINCT tbl_booking_details.txt_STAFFNAME AS txt_PILOT,
                           tbl_staff_details.int_LISTORDER AS int_ORDER
            FROM tbl_booking_details
            INNER JOIN tbl_staff_details
            ON
            tbl_staff_details.txt_PILOTNAME = tbl_booking_details.txt_STAFFNAME
            WHERE tbl_booking_details.date_DATE = '{$tl_date}'
                AND tbl_staff_details.txt_PILOTNAME !='--none--'
                AND tbl_booking_details.txt_COMPLETECODE !='CANCELLED')
            UNION
            (SELECT tbl_staff_details.txt_PILOTNAME AS txt_PILOT,
                    tbl_staff_details.int_LISTORDER AS int_ORDER
            FROM `tbl_staff_details`
            WHERE tbl_staff_details.bool_ISPILOT =1
                AND tbl_staff_details.bool_CURRENT =1
                AND tbl_staff_details.txt_PILOTNAME != '--none--'
                AND tbl_staff_details.int_LISTORDER <500)
            ORDER BY ".STAFF_LIST_ORDER;

这个查询扩展了我对MySQL的了解，所以我需要解决如何在UNION语句之前修改查询的第一部分，以获得'tbl_booking_detail‘中的飞行员(txt_STAFFNAME)和副飞行员(txt_STAFFNAME2)的列表，以及他们在'tbl_staff_detail’中的列表顺序(int_LISTORDER)，这些列表在特定的一天已经预订到一个表中，即'txt_PILOT‘和'int_ORDER’。希望WHERE子句中的字段和值的名称是自解释的。

问候

布雷登。

编辑：@bonCodigo

表的详细信息是(减去许多不相关的字段)：

tbl_booking_details
------------------------------------------------------------------------------
ID   |   txt_STAFFNAME | txt_STAFFNAME2 |  date_DATE | txt_COMPLETECODE  | ...
------------------------------------------------------------------------------
475  | Joe A           | Joe B          | 2013-01-14 | CONFIRMED         | ...
------------------------------------------------------------------------------
476  | Joe A           | --none--       | 2013-01-14 | CONFIRMED         | ...
------------------------------------------------------------------------------
476  | Joe C           | Joe A          | 2013-01-14 | CANCELLED         | ...
------------------------------------------------------------------------------
477  | Joe B           | Jane D         | 2013-01-14 | CONFIRMED         | ...
------------------------------------------------------------------------------
478  | Joe E           | Jane D         | 2013-01-14 | CONFIRMED         | ...
------------------------------------------------------------------------------



tbl_staff_details
----------------------------------------------------------------------------
ID | txt_PILOTNAME | bool_CURRENT| bool_ISPILOT |  int_LISTORDER | ...
----------------------------------------------------------------------------
1  | --none--      | 1           | 1            |  0             | ...
----------------------------------------------------------------------------
2  | Joe A         | 1           | 1            |  5             | ...
----------------------------------------------------------------------------
4  | Joe B         | 1           | 1            |  15            | ...
----------------------------------------------------------------------------
5  | Joe C         | 1           | 1            |  510           | ...
----------------------------------------------------------------------------
6  | Jane D        | 1           | 1            |  20            | ...
----------------------------------------------------------------------------
7  | Joe E         | 1           | 1            |  520           | ...
----------------------------------------------------------------------------

期望的结果：

--------------------------
| txt_PILOT |  int_ORDER |
--------------------------
| Joe A     |  5         |
--------------------------
| Joe B     | 15         |
--------------------------
| Jane D    | 20         |
--------------------------
| Joe E     | 520        |
--------------------------

Answer 1

经过进一步的研究，似乎我可能过度设计或复杂化了我的原始解决方案，因为我可能是从“错误的角度”来处理它的。对查询进行彻底的重新思考，并使用staff表作为查询的左侧，而不是右侧，得到以下结果，这似乎是可行的：

SELECT DISTINCT tbl_staff_details.txt_PILOTNAME AS txt_PILOTNAME,
                tbl_staff_details.int_LISTORDER AS int_LISTORDER
    FROM tbl_staff_details,tbl_booking_details
    WHERE tbl_booking_details.date_DATE = '{$tl_date}'
        AND tbl_booking_details.txt_COMPLETECODE != 'CANCELLED'
        AND tbl_staff_details.txt_PILOTNAME != '--none--'
        AND (tbl_staff_details.txt_PILOTNAME = tbl_booking_details.txt_STAFFNAME
         OR tbl_staff_details.txt_PILOTNAME = tbl_booking_details.txt_STAFFNAME2)
UNION
    SELECT tbl_staff_details.txt_PILOTNAME AS txt_PILOTNAME,
                tbl_staff_details.int_LISTORDER AS int_LISTORDER
    FROM `tbl_staff_details`
    WHERE tbl_staff_details.bool_ISPILOT =1
        AND tbl_staff_details.bool_CURRENT =1
        AND tbl_staff_details.txt_PILOTNAME != '--none--'
        AND tbl_staff_details.int_LISTORDER <500
    ORDER BY int_LISTORDER ASC, txt_PILOTNAME ASC

只是使用一个简单的连接。我以为只有当有一个飞行员和副驾驶的指数高于500时，它才会返回第一个飞行员的名字，但是如果我当天有一个预订，并且两个飞行员的指数都超过500，它就会返回两个人，所以成功了！

感谢任何看过并思考过它的人，即使他们没有发布任何东西或提出解决方案。

干杯

布雷登