CODEWITHSUNDEEP
mysqlsubquerycorrelated-subquery
Adwelean
Adwelean

Reputation: 47

MySQL multiple level subquery

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?

Upvotes: 0

Views: 1988

Answers (2)

shawnt00
shawnt00

Reputation: 17915

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

Upvotes: 2

MikeVe
MikeVe

Reputation: 1120

Use the full table name instead of the alias.

Replace every git.items_id with glpi_items_tickets.items_id.

The alias does not exist during the compilation of the subquery, since the outer query (where you set your alias) can not be compiled until the inner querys are considered valid.

Upvotes: 0

Related Questions