Referencing subquery in from clause from subquery in select clause?

Question

I need to reference a created table from my from clause. I have access to the user table inside the subqueries, but not the history table that was created. When I do a "select * from history" it returns results.

Code:

SELECT
user.username as username,
(SELECT COUNT(*) FROM history WHERE acceptedBy = user.username) AS acceptedCount,
(SELECT COUNT(*) FROM history WHERE completedBy = user.username) AS completedCount,
(SELECT COUNT(*) FROM history WHERE canceledBy = user.username) AS canceledCount
FROM (
        SELECT move.id AS id,
        move.acceptedBy AS acceptedBy,
        move.completedBy AS completedBy,
        move.canceledBy AS canceledBy
        FROM move_history AS move

        UNION

        SELECT drop_move.id AS id,
        drop_move.acceptedBy AS acceptedBy,
        drop_move.completedBy AS completedBy,
        drop_move.canceledBy AS canceledBy
        FROM drop_move_history AS drop_move

        UNION

        SELECT start_stage_move.id AS id,
        start_stage_move.acceptedBy AS acceptedBy,
        start_stage_move.completedBy AS completedBy,
        NULL AS canceledBy
        FROM start_stage_move_history AS start_stage_move

        UNION

        SELECT finish_stage_move.id AS id,
        finish_stage_move.acceptedBy AS acceptedBy,
        finish_stage_move.completedBy AS completedBy,
        finish_stage_move.canceledBy AS canceledBy
        FROM finish_stage_move_history AS finish_stage_move
) AS history, user

Results:

Base table or view not found: 1146 Table 'yard_management.history' doesn't exist

Answer 1

You need to define it as a Common Table Expression (CTE).

I note that having four tables with the exact same schema is a code smell. They should probably all be in one table.

Also, favour UNION ALL over UNION, because the latter forces de-duplication (it implies DISTINCT) which is often unnecessary.

WITH history AS (
        SELECT move.id AS id,
        move.acceptedBy AS acceptedBy,
        move.completedBy AS completedBy,
        move.canceledBy AS canceledBy
        FROM move_history AS move

        UNION ALL

        SELECT drop_move.id AS id,
        drop_move.acceptedBy AS acceptedBy,
        drop_move.completedBy AS completedBy,
        drop_move.canceledBy AS canceledBy
        FROM drop_move_history AS drop_move

        UNION ALL

        SELECT start_stage_move.id AS id,
        start_stage_move.acceptedBy AS acceptedBy,
        start_stage_move.completedBy AS completedBy,
        NULL AS canceledBy
        FROM start_stage_move_history AS start_stage_move

        UNION ALL

        SELECT finish_stage_move.id AS id,
        finish_stage_move.acceptedBy AS acceptedBy,
        finish_stage_move.completedBy AS completedBy,
        finish_stage_move.canceledBy AS canceledBy
        FROM finish_stage_move_history AS finish_stage_move
)
SELECT
user.username as username,
(SELECT COUNT(*) FROM history h WHERE h.acceptedBy = user.username) AS acceptedCount,
(SELECT COUNT(*) FROM history h WHERE h.completedBy = user.username) AS completedCount,
(SELECT COUNT(*) FROM history h WHERE h.canceledBy = user.username) AS canceledCount
FROM user