VIEW and JOIN, return the product, and the value with the latest date!

Question

I have the following tables 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 running the VIEW, return all products with their last value. That is the value that has the latest product date.

When I consult my VIEW, the data is returned as follows:

SELECT * FROM view_site_products;

VIEW SQL:

CREATE OR REPLACE VIEW view_site_produtos AS
SELECT p.codigo, p.nome, p.url, pp.valor FROM produto p
  LEFT JOIN produto_preco pp ON p.codigo = pp.cod_produto
GROUP BY p.codigo;

I’ve tried with ORDER BY, and I haven’t been able to!

Note that the product Orange is worth 20

SELECT * FROM product_preco;

Being that the correct value is 18, as it has the most recent date!

Any of you have a suggestion, or a solution to how I can make this work?

Answer 1

As the need was to fetch the price of the product with the highest date, it is only to make a sub-select that looks for the highest date in the table produto_preco and filter the field data.

Example

Table data produto

| codigo |    nome |
|--------|---------|
|      1 | Laranja |
|      2 |    Maça |
|      3 |    Kiwi |
|      4 |    Pera |

Table data produto_preco

| valor | cod_produto |                 data |
|-------|-------------|----------------------|
|    30 |           3 | 2018-02-18T17:23:12Z |
|  4.44 |           2 | 2018-02-14T17:23:12Z |
|    18 |           1 | 2018-02-22T17:23:12Z |
|    17 |           1 | 2018-02-17T17:23:12Z |
|    15 |           1 | 2018-02-14T17:23:12Z |
|    20 |           1 | 2018-02-14T17:23:12Z |

As in the question does not have if it was to list only the products that has price or the all the products I will leave the 2 examples.

List only product with price

In the SQL below, the left join table produto with the table produto_preco. But the command pp.data = (select max(data) from produto_preco where cod_produto = p.codigo) transforms the left join in inner join.

SELECT
  p.codigo, p.nome, pp.valor, pp.data
FROM
  produto p
  LEFT JOIN produto_preco pp ON p.codigo = pp.cod_produto
where
  pp.data = (select max(data) from produto_preco where cod_produto = p.codigo)

SQL result does not bring the product Pear.

| codigo |    nome | valor |                 data |
|--------|---------|-------|----------------------|
|      3 |    Kiwi |    30 | 2018-02-18T17:23:12Z |
|      2 |    Maça |  4.44 | 2018-02-14T17:23:12Z |
|      1 | Laranja |    18 | 2018-02-22T17:23:12Z |

List all products with or without price

In the SQL below, the left join table produto with the table produto_preco, but in this example the command pp.data = (select max(data) from produto_preco where cod_produto = p.codigo) is in the left join, thus maintaining the left join.

SELECT
  p.codigo, p.nome, pp.valor, pp.data
FROM
  produto p
  LEFT JOIN produto_preco pp ON 
    p.codigo = pp.cod_produto and
    pp.data = (select max(data) from produto_preco where cod_produto = p.codigo)

Result is all products, but no in the field valor and data.

| codigo |    nome |  valor |                 data |
|--------|---------|--------|----------------------|
|      3 |    Kiwi |     30 | 2018-02-18T17:23:12Z |
|      2 |    Maça |   4.44 | 2018-02-14T17:23:12Z |
|      1 | Laranja |     18 | 2018-02-22T17:23:12Z |
|      4 |    Pera | (null) |               (null) |

Practical example

Only products with price

http://sqlfiddle.com/#! 9/7e803/16

With all the products

http://sqlfiddle.com/#! 9/fd7de9/12

Answer 2

Try to make the View SQL like this:

    SELECT p.codigo
         , p.nome
         , p.url
         , pp.valor 
      FROM produto p
      LEFT JOIN produto_preco pp ON p.codigo = pp.cod_produto
     WHERE pp.data = (SELECT MAX(data) 
                        FROM produto_preco 
                       WHERE produto_preco.codigo = p.codigo)
  GROUP BY p.codigo;