PDOException：SQLSTATE [23000]：完整性约束违规：1052 IN / ALL / ANY子查询中的列'category_id'不明确

Question

got a select within a select statement and I'm not sure why the result is ambiguous!

my (php) function:

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;
        }

    }

I specified which ID in the second select and I still get the error. (I researched, came across PHP MySQL - Column 'id' in IN/ALL/ANY subquery is ambiguous amended my second select so it specifies which ID to read and same error)

Any help given would be much appreciated :D

Answer 1

You have to add the alias from wich column you id should be:

Please change:

WHERE category_id IN

TO

WHERE C.category_id IN