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
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
in subquery
SELECT category_id FROM event_in_categories