I have a query that is returning null when nested as a subquery using PDO. If I run this query in MySQL it works as expected SELECT * FROM (SELECT id, name FROM MyReport1 m1 UNION SELECT id,name FROM MyReport2 m2) agg However this select / subquery doesn't work when calling via PDO in PHP.
The following works:
$lookupQuery = $dbs->prepare("SELECT id, name FROM MyReport1 m1 UNION SELECT id,name FROM MyReport2 m2");
$lookupQuery->execute();
$row_lookupQuery = $lookupQuery->fetchAll( PDO::FETCH_ASSOC );
$totalRows_lookupQuery = $lookupQuery->rowCount();
This however does not (returns null):
$lookupQuery = $dbs->prepare("SELECT * FROM (SELECT id, name FROM MyReport1 m1 UNION SELECT id,name FROM MyReport2 m2) agg");
$lookupQuery->execute();
$row_lookupQuery = $lookupQuery->fetchAll( PDO::FETCH_ASSOC );
$totalRows_lookupQuery = $lookupQuery->rowCount();
For the sake of the example the table names etc. are changed, but the principle is the same. The reason I want to do this is because I want to then run a GROUP BY on the aggregated output of the subquery.
