Query of two values for a Column

Question

It has a table named Preco. Inside this table if you find the sale price and the cost of the product however, I cannot print in echo pq the name of the column is one and does not specify who is sale or cost.

EDIT Query to fetch product price:

        SELECT Produto.Nome as Nome_do_Produto, Codigo_Fabricante1, Qtde, ProdutoPreco.Preco, TabelaPreco.Nome as Tabela, TabelaPreco__Ide
            FROM TabelaPreco
            INNER JOIN ProdutoPreco
            ON ProdutoPreco.TabelaPreco__Ide = TabelaPreco.Ide
            INNER JOIN Produto
            ON ProdutoPreco.Produto__Codigo = Produto.Codigo
            INNER JOIN Estoque_Atual
            ON Produto.Codigo = Estoque_Atual.Produto 
            WHERE Filial = 1 AND (Produto.Nome like '%IMPERADOR%') AND Preco > 0 AND Qtde > 0

Result of the above consultation:

Nome_do_Produto Codigo_Fabricante1  Qtde    Preco   Tabela  TabelaPreco__Ide
MAC ESPAG IMPERADOR FORTALEZA 500G  7891152234015   1.00000 2.2900000000    Preco   11627049-F321-42DE-A3ED-4101BADDBC32
MAC ESPAG IMPERADOR FORTALEZA 500G  7891152234015   1.00000 1.7000000000    Custo   1D05193A-5045-4592-AA17-E2C1EA4D9260
MAC PARAFUSO IMPERADOR 500G 7891152334142   41.00000    3.1900000000    Preco   11627049-F321-42DE-A3ED-4101BADDBC32
MAC PARAFUSO IMPERADOR 500G 7891152334142   41.00000    2.1800000000    Custo   1D05193A-5045-4592-AA17-E2C1EA4D9260

I need to print the two values separately in php but the values are in only one column (Price).

as I defined in php:

echo "EAN: " . $row_usuario['Codigo_Fabricante1'] . "<br>";
echo "Nome: " . $row_usuario['Name1'] . "<br>";
echo "Preco de Custo: R$";  echo number_format((float)$row_usuario['Preco'], 2, ',', '.'). "<br>";
echo "Preco de Venda: R$";  echo number_format((float)$row_usuario['Preco'], 2, ',', '.'). "<br>";
echo "Estoque: ";   echo number_format((float)$row_usuario['Qtde'], 2, ',', '.'). "<br><br>";

Answer 1

It is not very clear in the question, but by queries and description it is possible to notice that each product has 2 price records in the table TabelaPreco. When TabelaPreco.Nome = 'Custo' one has the cost of it and when TabelaPreco.Nome = 'Preco' you have the selling price.

There are numerous ways to solve this problem and most of them, to facilitate the use in PHP, should join the 2 queries presented in a single query.

Since not all information necessary for an error-free response has been provided, take the example below as a basis. The important thing is to have 2 records of TabelaPreco for each product and this can be done with a inner join, as shown below.

SELECT Produto.Nome as Nome_do_Produto, Codigo_Fabricante1, Qtde, TabelaPreco.Preco, TabelaPreco_Custo.Preco AS Custo, TabelaPreco.Nome as Tabela, TabelaPreco__Ide
FROM TabelaPreco
INNER JOIN TabelaPreco AS TabelaPreco_Custo
  ON TabelaPreco_Custo.Ide = TabelaPreco.Ide
  AND TabelaPreco_Custo.Nome = 'Custo'
INNER JOIN ProdutoPreco
  ON ProdutoPreco.TabelaPreco__Ide = TabelaPreco.Ide
INNER JOIN Produto
  ON ProdutoPreco.Produto__Codigo = Produto.Codigo
INNER JOIN Estoque_Atual
  ON Produto.Codigo = Estoque_Atual.Produto 
WHERE
  Filial = 1
  AND (Produto.Nome like '%IMPERADOR%') 
  AND Preco > 0
  AND Qtde > 0 
  AND TabelaPreco.Nome = 'Preco'

Note that one of the queries was kept (the one that picked up the price) and was added a INNER JOIN with the same table, to find the cost, with the section below. In this case it is important to confirm how to join the records of the same table (based on Join done with the table ProdutoPreco) and add table identifiers TabelaPreco, to resolve query errors.

INNER JOIN TabelaPreco AS TabelaPreco_Custo
  ON TabelaPreco_Custo.Ide = TabelaPreco.Ide
  AND TabelaPreco_Custo.Nome = 'Custo'

Note: Again I would like to point out that the above query may not be correct due to the lack of details of the question. It is impossible to know, for example, whether Preco is in TabelaPreco, ProdutoPreco or any other table.