Creating view in Mysql

Asked

Viewed 28 times

-1

I have a structure for view in the database. And when executing, the error is that c3.nome does not exist. But exists in the database, I believe it is on account of subquery. How to proceed?

SELECT
    c1.quantidade,
    c1.valor,
    c1.data_compra,
    c2.razao_social,
    c3.nome,
    c4.usuario
FROM
    ferramental_estoque AS c1
LEFT JOIN fornecedor AS c2 ON c2.id_fornecedor = c1.id_fornecedor
WHERE
    c2.situacao IN (
        SELECT
            *
        FROM
            fornecedor
        WHERE
            situacao = '0'
    );
LEFT JOIN ativo_externo AS c3 ON c3.id_ativo_externo = c1.id_ativo_externo
LEFT JOIN usuario AS c4 ON c4.id_usuario = c1.id_usuario
mysql view
  • 3

    these LEFT JOIN should not come before the Where?

  • 2

    Beyond what has already been said here c2.situacao IN (SELECT * FROM fornecedor WHERE situacao = '0' ) your subquery should return only the field situacao and not all fields in the table, and what is the meaning of this subquery if it will always return '0'?

1 answer

3


What happens is that the database in the course of the execution it does a validation of your code, taking the list of existing attributes in SELECT and searching if it exists in your tables, as the c3.nome is the first attribute he found that there is no it already for, because if there is an error for him to execute, you do not need to continue that it will not work, correcting your code meant staying like this.

SELECT
    c1.quantidade,
    c1.valor,
    c1.data_compra,
    c2.razao_social,
    c3.nome,
    c4.usuario
FROM
    ferramental_estoque AS c1
LEFT JOIN fornecedor AS c2 ON c2.id_fornecedor = c1.id_fornecedor
LEFT JOIN ativo_externo AS c3 ON c3.id_ativo_externo = c1.id_ativo_externo
LEFT JOIN usuario AS c4 ON c4.id_usuario = c1.id_usuario
WHERE
    c2.situacao IN (
        SELECT
            situacao
        FROM
            fornecedor
        WHERE
            situacao = '0'
    );

Another point to be taking into account is that IN compares an attribute with a list of the same type, so if you should have a list of only one attribute and not several attributes as you did

Browser other questions tagged mysql view

You are not signed in. Login or sign up in order to post.