MySQL multiple level subquery

Question

I try to use a field from joined table "glpi_items_tickets" into a subquery like this :

    SELECT
        NAME
    FROM
        (
            SELECT
                NAME
            FROM glpi_computers
            WHERE id = git.items_id
        ) AS t1
    UNION
        (
            SELECT
                NAME
            FROM glpi_monitors
            WHERE   id = git.items_id
        )
    UNION
        (
            SELECT
                NAME
            FROM glpi_networkequipments
            WHERE   id = git.items_id
        )
    UNION
        (
            SELECT
                NAME
            FROM glpi_printers
            WHERE id = git.items_id
        )
) AS aliasIT

but i have an error of type : "#1054 - Unknown Column 'git.items_id' in where clause"

The entire request :

SELECT
    gt.id,
    (
        SELECT
            NAME
        FROM
            (
                SELECT
                    NAME
                FROM glpi_computers
                WHERE   id = git.items_id
            ) AS t1
        UNION
            (
                SELECT
                    NAME
                FROM glpi_monitors
                WHERE   id = git.items_id
            )
        UNION
            (
                SELECT
                    NAME
                FROM glpi_networkequipments
                WHERE id = git.items_id
            )
        UNION
            (
                SELECT
                    NAME
                FROM glpi_printers
                WHERE id = git.items_id
            )
    ) AS aliasIT
FROM glpi_tickets gt
INNER JOIN glpi_items_tickets git 
ON gt.id = git.tickets_id;

Do you have a solution for used the field "glpi_items_tickets.items_id" into my subqueries?

Answer 1

SELECT ...
FROM
    glpi_tickets gt
    INNER JOIN glpi_items_tickets git 
        ON git.tickets_id = gt.id;
    INNER JOIN
    (
        SELECT NAME, id FROM glpi_computers
        UNION
        SELECT NAME, id FROM glpi_monitors
        UNION
        SELECT NAME, id FROM glpi_networkequipments
        UNION
        SELECT NAME, id FROM glpi_printers
    ) g
        ON g.id = git.items_id