php - PDO异常 : SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'category_id' in IN/ALL/ANY subquery is ambiguous

Question

在 select 语句中进行了选择，但我不确定为什么结果不明确!

我的(php)函数:

function FunctionName($EventId) {

    $database = new Connection();
    $database = $database->Connect();
    $statement = $database->prepare(" SELECT C.*
        FROM event_categories AS C
        LEFT JOIN event_in_categories as EC
        ON C.category_id = EC.category_id
        LEFT JOIN events AS E
        ON EC.EventId = E.EventId
        WHERE category_id IN

        (
            SELECT EC.category_id FROM event_in_categories
            WHERE EventId = :EventId
        )

        AND category_active = 1 ");

        $statement->execute(array("EventId" => $EventId));
        $Categories = $statement->fetchall(PDO::FETCH_ASSOC);

        if (!empty($Categories)) {
            foreach ($Categories as $Category) {
                $CategoryArray[] = new EventCategory(null, $Category);
            }
            return $CategoryArray;
        }

    }

我在第二个选择中指定了哪个 ID，但仍然收到错误。 (我研究了一下，发现 PHP MySQL - Column 'id' in IN/ALL/ANY subquery is ambiguous 修改了我的第二个选择，以便它指定要读取的 ID 和相同的错误)

任何提供的帮助将不胜感激:D

Answer 1

您必须从您 id 应该是的列中添加别名:

请更改:

WHERE category_id IN

致

WHERE C.category_id IN