Re: Selecting multiple child tables throwing error

[Aaron J]

Please find details below.


Does the query elicit the same error when run directly in the mysql client program?
Yes.

is the server running on Windows without case sensitivity?
Yes


Query below.

SELECT
`Project4`.`OrderId`,
`Project4`.`CustomerId`

FROM (SELECT
`Project1`.`OrderId`,
`Project1`.`CustomerId`

CASE WHEN (`UnionAll1`.`ItemId` IS NOT NULL) THEN (1) ELSE (NULL) END AS `C86`
FROM (SELECT
`Extent1`.`OrderId`,
`Extent1`.`CustomerId`

FROM `ord.Orders` AS `Extent1`
WHERE `Extent1`.`OrderId` = 5) AS `Project1` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent4`.`UnitPriceId` IS NOT NULL) THEN (1) ELSE (NULL) END AS `C1`,
`Extent2`.`ItemId`,
`Extent2`.`ItemId` AS `ItemId1`

FROM `ord.Items` AS `Extent2` INNER JOIN `prd.EntityA` AS `Extent3` ON `Extent2`.`EntityAId` = `Extent3`.`EntityAId` LEFT OUTER JOIN `prd.ChildEndityA` AS `Extent4` ON `Extent4`.`EntityAId` = `Extent2`.`EntityAId`
WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`,
`Extent5`.`ItemId`,
`Extent5`.`ItemId` AS `ItemId1`

FROM `ord.Items` AS `Extent5` INNER JOIN `prd.EntityA` AS `Extent6` ON `Extent5`.`EntityAId` = `Extent6`.`EntityAId` INNER JOIN `prd.ChildEndityB` AS `Extent7` ON (`Extent5`.`EntityAId` = `Extent6`.`EntityAId`) AND (`Extent7`.`EntityAId` = `Extent6`.`EntityAId`)
WHERE 1 = 1)) AS `UnionAll1` ON `Extent1`.`OrderId` = `UnionAll1`.`OrderId`) AS `Project4` ORDER BY `Project4`.`OrderId` ASC


The problem with the query is in last line, `Extent1`.`OrderId` referring a invalid alias. `

If I change the alias manually to `Project1.OrderId`, query is working fine.


Thanks.