I have the following tables that I created.
CREATE TABLE produto (
codigo INT UNSIGNED NOT NULL AUTO_INCREMENT,
nome VARCHAR(128) NOT NULL,
url VARCHAR(255) NOT NULL,
qde_min SMALLINT UNSIGNED NOT NULL,
prazo_entrega TINYINT UNSIGNED NOT NULL,
descricao VARCHAR(1000) DEFAULT NULL,
status TINYINT(1) DEFAULT '0',
PRIMARY KEY (codigo)
);
CREATE TABLE produto_preco (
valor DOUBLE(9, 2) NOT NULL,
cod_produto INT UNSIGNED NOT NULL,
data DATETIME NOT NULL,
FOREIGN KEY (cod_produto) REFERENCES produto (codigo)
);
I need that when I run the VIEW, return all products with their last value. That is the value that has the most recent product date.
When I query my VIEW, the data is returned as follows:
SELECT * FROM view_site_products;
VIEWSQL:
CREATEORREPLACEVIEWview_site_produtosASSELECTp.codigo,p.nome,p.url,pp.valorFROMprodutopLEFTJOINproduto_precoppONp.codigo=pp.cod_produtoGROUPBYp.codigo;
IhavetriedwithORDERBY,andneitherdidit!
NotethattheproductOrangeisworth20
SELECT*FROMproduct_preco;
Being the correct value is 18 because it has the most recent date!
Does anyone have a suggestion, or solution how can I make it work?