VIEW and JOIN, return the product, and the value with the most recent date!

Question

VIEW and JOIN, return the product, and the value with the most recent date!

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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?

mysql sql

asked by anonymous 23.02.2018 / 21:24

2 answers

0

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;

23.02.2018 / 21:46

Action call as parameter in Html.BeginForm - ASP .NET MVC 5 Remove tag a using php and leave only td text

score 1 · Accepted Answer

As the need was to get the price of the product with the largest date, it is only to make a sub-select that searches the longest date in the produto_preco table and filters the data field.

Example

Data table 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 the question does not have if it was to list only the products that have price or all the products I will leave the 2 examples.

List only product with price

In the SQL below, the left join of the produto table is being done with the produto_preco table. But the pp.data = (select max(data) from produto_preco where cod_produto = p.codigo) command transforms left join into 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 Pera product.

| 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 of the produto table is being made with the produto_preco table, but in this example the pp.data = (select max(data) from produto_preco where cod_produto = p.codigo) command is in left join , thus keeping 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 null in valor and data field.

| 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 exam

Only priced products

link

With all products

link