MySQL select语句出错 - MySQL select statement giving error

这是我的MySQL select语句，它给了我错误-"on clause"中的未知列"Regattas.regatta_id"

    SELECT
        Regattas.regatta_id, 
        Events.event_id,
        Events.event_name
        FROM Regattas, Events
        LEFT JOIN Regatta_Events AS Regatta_Events_1 ON Regatta_Events_1.fk_event_id = Events.event_id 
        LEFT JOIN Regatta_Events AS Regatta_Events_2 ON Regatta_Events_2.fk_regatta_id = Regattas.regatta_id
        WHERE Regattas.regatta_id = {$regattaId}

表格的布局如下：

Regattas表：

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| regatta_id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| regatta_name       | varchar(100) | NO   |     | NULL    |                |
| regatta_start_date | date         | NO   |     | NULL    |                |
| regatta_end_date   | date         | NO   |     | NULL    |                |
| regatta_start_time | time         | NO   |     | NULL    |                |
| regatta_venue_id   | int(11)      | NO   |     | 0       |                |
+--------------------+--------------+------+-----+---------+----------------+

事件表：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          | 
+------------+--------------+------+-----+---------+----------------+
| event_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| event_name | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Regatta_Events表如下-连接表：

+-------------------+---------+------+-----+---------+----------------+   
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| regatta_events_id | int(11) | NO   | PRI | NULL    | auto_increment |
| fk_regatta_id     | int(11) | NO   |     | 0       |                |
| fk_event_id       | int(11) | NO   |     | 0       |                |
+-------------------+---------+------+-----+---------+----------------+

请帮我解决这个问题我已经做了一段时间了。

假设这就是您想要做的，您可以使用UNION ALL。你不需要第一个查询的OUTER JOIN——我把它留作参考（不是100%肯定你想要实现的）：

SELECT
        Regattas.regatta_id, 
        NULL event_id,
        NULL event_name
FROM Regattas
        LEFT JOIN Regatta_Events ON Regatta_Events.fk_regatta_id = Regattas.regatta_id
WHERE Regattas.regatta_id = {$regattaId}
UNION ALL
SELECT
        NULL regatta_id, 
        Events.event_id,
        Events.event_name
FROM Events
        LEFT JOIN Regatta_Events ON Regatta_Events.fk_event_id = Events.event_id

我不完全确定我是否理解你想要的结果。这将返回Events表中的所有结果，并且仅返回Regattas表中id在输入中匹配的结果。

也许你正在寻找这样的东西：

SELECT
    Regattas.regatta_id, 
    Events.event_id,
    Events.event_name
FROM Regattas
    LEFT JOIN Regatta_Events ON Regatta_Events.fk_regatta_id=Regattas.regatta_id
    LEFT JOIN Events ON Regatta_Events.fk_event_id=Events.event_id 
WHERE Regattas.regatta_id = {$regattaId}

我认为这应该是一个简单的联接，以返回Regetta_id、event_id和event_name组合。

SELECT
        Regattas.regatta_id, 
        Events.event_id,
        Events.event_name
        FROM Regattas as rg
        LEFT JOIN Regatta_Events as re ON re.fk_event_id = rg.regatta_id 
        LEFT JOIN Events AS ev ON re.fk_regatta_id = ev.event_id
        WHERE rg.regatta_id = {$regattaId}