3 tables sql sum divided by a minus between two fields not working

January 19, 2023

I have 3 tables: formulario, viaverde and reparacoes.
one of the fields is equal in all tables, called matricula, i’m trying to group every arithmetic operation to it.
but i keep getting the error of matricula is ambiguous or synthax errors, how can i achieve this?

i tried the following code:

JOIN (
    SELECT matricula, (total)/(kmsTotais) AS custoKM
    FROM (
        SELECT matricula, SUM(kmfim - quilometragem) AS kmsTotais
        FROM formulario
        GROUP BY matricula
    ) e
    JOIN (
        SELECT matricula, abastecimento_euros as total FROM formulario
        UNION ALL
        SELECT matricula, custo as total FROM viaverde
        UNION ALL
        SELECT matricula, valor as total FROM reparacoes
    ) as subquery
    GROUP BY matricula
) i ON i.matricula = f.matricula

>Solution :

You need to qualify matricula with the table aliases to make it unambiguous.

JOIN (
    SELECT e.matricula, SUM(total)/MAX(kmsTotais) AS custoKM
    FROM (
        SELECT matricula, SUM(kmfim - quilometragem) AS kmsTotais
        FROM formulario
        GROUP BY matricula
    ) e
    JOIN (
        SELECT matricula, abastecimento_euros as total FROM formulario
        UNION ALL
        SELECT matricula, custo as total FROM viaverde
        UNION ALL
        SELECT matricula, valor as total FROM reparacoes
    ) as subquery ON e.matricula = subquery.matricula
    GROUP BY e.matricula
) i ON i.matricula = f.matricula

You could also replace ON e.matricula = subquery.matricula with USING (matricula). Since this indicates that the column name is the same in both tables, you don’t need to qualify that column.