Every derived table must have its own alias

[Patrick Flynn]

I am stuck on this error: Every derived table must have its own alias I have been attempting to have a property in my entity that is called "InUse" which runs a formula (sql) to check 2 different tables for the same column and to see if it exists in either table. But for some reason every way I have written this query it throws an error that I cannot get past. Here is the example. Note: locationID is the primary key property of this entity.

SELECT itemLocation

                                                        FROM (

                                                                        SELECT itemLocation

                                                                        FROM store

                                                                        WHERE itemLocation = locationID

                                                                        UNION ALL

                                                                        SELECT itemLocation

                                                                        FROM warehouse

                                                                        WHERE itemLocation = locationID

                                                                )

                                                        GROUP BY itemLocation

                                                        HAVING Count(*) = 1

and if I name the 2 table union outside of the parentheses AS mydata (which is my derivative table) it throws an error right there about syntax. Thoughts?

[Ancient Programmer]

You need to give an alias to the tables. Otherwise, ORM won't be able to differentiate column and property. 

SELECT l.itemLocation
FROM (
    SELECT s.itemLocation
    FROM store AS s
    WHERE s.itemLocation = locationID


    UNION ALL


    SELECT w.itemLocation
    FROM warehouse AS w
    WHERE w.itemLocation = locationID
) AS l
GROUP BY l.itemLocation
HAVING COUNT( * ) = 1;

[Patrick Flynn]

Thanks, I have attempted this similar query but I am receiving an error on giving an alias to the union overall (the AS l part from your example).

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.l GROUP BY l.itemLocation HAVING COUNT( * ) = 1) as formula1093_0_ from warehou' at line 14

Thoughts? I am running Lucee 5. The query runs just fine on MySQL but not in the orm.

[Ancient Programmer]

Yeah... I got the same result. It seemed to me Hibernate could not generate the right SQL statement.

Try a different approach. Create a VIEW that unions those two tables?