mysql - 如何加入_first_记录？

Question

由于我对复杂的 mysql 查询的了解有限，我正在尝试从数据库中检索一些信息。

故事是这样的；用户收到邀请来我们公司。在这个邀请的基础上，用户可以获得多个通知和多个约会。我有三个相关的表:

invites
-------
| id | name    | created    |
-----------------------------
|  1 | someth1 | 2018-02-03 |
|  2 | someth2 | 2018-02-03 |
|  3 | someth3 | 2018-02-03 |

notifications
-------------
| id | inv_id  | message |
--------------------------
|  1 | 101     | hello   |
|  2 | 287     | hi      |
|  3 | 827     | hey     |

appointments
------------
| id | inv_id  | start_at   |
-----------------------------
|  1 | 101     | 2018-02-03 |
|  2 | 287     | 2018-02-08 |
|  3 | 827     | 2018-02-15 |

我目前有一个查询，它显示发送给用户的通知列表，用于 2018 年 2 月 1 日之后完成的所有邀请，并且预约不迟于“2018-03-10”。

SELECT id, inv_id, message
FROM notifications 
WHERE inv_id IN (
    SELECT id
    FROM invites as invite
    WHERE created > '2018-02-01'
    AND id IN (
        SELECT inv_id 
        FROM appointments
        WHERE invite.id = inv_id
        AND start_at < '2018-03-10'
    )
)
ORDER BY inv_id ASC;

结果看起来像这样:

| id | inv_id  | message |
--------------------------
|  1 | 101     | hello   |
|  2 | 287     | hi      |
|  3 | 827     | hey     |

我现在想为这些通知添加第一次约会的start_at

| id | inv_id  | message | start_at   |
---------------------------------------
|  1 | 101     | hello   | 2018-02-03 |
|  2 | 287     | hi      | 2018-02-08 |
|  3 | 827     | hey     | 2018-02-15 |

但是从这里开始我有点迷失了我应该怎么做。

有谁知道我如何添加第一次约会的start_at，它对应于相应通知的邀请？所以它应该显示通知 inv_id 邀请的第一个约会的 start_at？

Answer 1

试试这个:

SELECT id, N.inv_id, message,A.start_at
FROM notifications N
JOIN(
    SELECT inv_id,MIN(start_at) start_at 
    FROM appointments 
    WHERE inv_id IN (
                SELECT id
                FROM invites as invite
                WHERE created > '2018-02-01'
                AND id IN (
                    SELECT inv_id 
                    FROM appointments
                    WHERE invite.id = inv_id
                    AND start_at < '2018-03-10'
                )
            )
    GROUP BY inv_id
    )A ON N.inv_id = A.inv_id
WHERE inv_id IN (
    SELECT id
    FROM invites as invite
    WHERE created > '2018-02-01'
    AND id IN (
        SELECT inv_id 
        FROM appointments
        WHERE invite.id = inv_id
        AND start_at < '2018-03-10'
    )
)
ORDER BY inv_id ASC;