Nest several tables to show records with the most recent date

Question

Nest several tables to show records with the most recent date

Navigation

#1 by (0 votes)

1

I have a question with a query, I need to make a query that shows me the most recent date of the registration of a price of a product that has a record of several prices with different dates, I just want to obtain the most recent date for each product, and for this I have the following tables

and my query is as follows

SELECT  hp.id_historico, 
        p.id_producto, 
        p.codigo_producto, 
        p.nomenclatura, 
        p.subnomenclatura, 
        p.clasificacion, 
        p.unidad_medida, 
        p.subcategoria, 
        p.nombre_producto, 
        p.gramaje, 
        p.entrada_salida, 
        p.status_producto, 
        p.stock, 
        p.id_categoria, 
        p.last_code, 
        CONCAT( u.firstname, ' ', u.lastname, ' ', u.user_perfil ) AS USER, 
        m.marca, 
        hp.precio_unitario, 
        hp.iva, 
        hp.precio_venta, 
        hp.fecha 
FROM    h_precio_prod hp, 
        products p, 
        users u, 
        marca m 
WHERE   hp.id_producto = p.id_producto 
AND     hp.user_id = u.user_id 
AND     hp.id_marca = m.id_marca 
GROUP BY hp.id_producto 
ORDER BY hp.fecha DESC

The detail with my query is that it shows me the oldest date instead of the most recent and when using a sub query it returns an error in the format of the date

Subquery

SELECT hp.id_historico, 
        p.id_producto, 
        p.codigo_producto, 
        p.nomenclatura, 
        p.subnomenclatura, 
        p.clasificacion, 
        p.unidad_medida, 
        p.subcategoria, 
        p.nombre_producto, 
        p.gramaje, 
        p.entrada_salida, 
        p.status_producto, 
        p.stock, 
        p.id_categoria, 
        p.last_code, 
        CONCAT( u.firstname, ' ', u.lastname, ' ', u.user_perfil ) AS USER, 
        m.marca, 
        hp.precio_unitario, 
        hp.iva, 
        hp.precio_venta, 
        hp.fecha 
FROM    h_precio_prod hp, 
        products p, 
        users u, 
        marca m 
WHERE   hp.id_producto = (SELECT MAX(hp.fecha) FROM h_precio_prod hp) 
AND     hp.user_id = u.user_id 
AND     hp.id_marca = m.id_marca 
GROUP BY hp.id_producto ORDER BY hp.fecha DESC

mysql sql query

asked by Fernando 20.12.2018 в 19:08

source

1 answer

Slow when taking pictures with Expo Camera Find a data from a table and place that data in another ORACLE

score 0 · Accepted Answer

The subquery does not work because you are comparing the id_producto which is data type int versus fecha with a data type datetime .

I prepared a shorter version of the relevant tables for the solution:

create table productos (
id_producto  int not null,
cod_producto  varchar(10) not null,
nombre_producto varchar(64) not null,
stock   int  not null
)

create table historico
(
id_historico int not null,
id_producto int not null,
user_id int not null,
precio_unitario money not null,
fecha   datetime not null,
)

We entered similar data:

insert into productos values (4350, 'SSM0013-1', 'Botiquin',2)
insert into productos values (4351, 'SAA0208-2', 'Gelatina',20)
insert into productos values (4352, 'SCP0016-1', 'Tamales',2)


insert into historico values (4231, 4351, 1, 02, '01/28/2018')
insert into historico values (4232, 4352, 1, 02, '01/28/2018')
insert into historico values (4233, 4352, 1, 02, '01/29/2018')
insert into historico values (4234, 4352, 2, 02, '01/30/2018')
insert into historico values (4235, 4350, 2, 02, '01/30/2018')
insert into historico values (4236, 4352, 2, 02, '01/31/2018')
insert into historico values (4237, 4352, 2, 02, '02/01/2018')
insert into historico values (4238, 4352, 2, 02, '02/02/2018')

I suggest not trying to solve the whole problem, but to separate it in the most basic queries and then go to the more complex ones.

The first part should be to obtain the most recent date of each product:

select  id_producto id_prod, max(fecha) fecha_reciente
from    historico 
group by id_producto

This gives us the following information:

id_prod     fecha_reciente            
----------- ------------------------- 
4352        2018-02-02 00:00:00.0     
4350        2018-01-30 00:00:00.0     
4351        2018-01-28 00:00:00.0     
(3 rows)Execution time: 0.034 seconds

Now, we compare the id and the date to get the rest of the information from the historical table. We have to do it as a subquery since doing it on the main query, we would not be able to group only by id_producto and therefore we would not get the most recent date:

select  his.precio_unitario,
        his.fecha,
        his.user_id
from historico his, 
            (
               select id_producto id_prod, max(fecha) fecha_reciente
               from historico 
               group by id_producto 
            ) as sub_consulta
where his.id_producto = sub_consulta.id_prod
and   his.fecha = sub_consulta.fecha_reciente

This returns us:

precio_unitario       fecha                     user_id     
--------------------- ------------------------- ----------- 
2.0000                2018-02-02 00:00:00.0     2           
2.0000                2018-01-30 00:00:00.0     2           
2.0000                2018-01-28 00:00:00.0     1           
(3 rows)Execution time: 0.035 seconds

And now, we add the join to the product table:

select      prod.id_producto,
            prod.cod_producto,
            prod.nombre_producto,
            prod.stock,
            his.precio_unitario,
            his.fecha,
            his.user_id
from historico his, 
      (
            select id_producto id_prod, max(fecha) fecha_reciente
            from historico 
            group by id_producto 
      ) as sub_consulta,
        productos   prod
where his.id_producto = sub_consulta.id_prod
and   his.fecha = sub_consulta.fecha_reciente
and    prod.id_producto = his.id_producto

To get the information we want:

id_producto cod_producto nombre_producto                                                  stock       precio_unitario       fecha                     user_id     
----------- ------------ ---------------------------------------------------------------- ----------- --------------------- ------------------------- ----------- 
4352        SCP0016-1    Tamales                                                          2           2.0000                2018-02-02 00:00:00.0     2           
4350        SSM0013-1    Botiquin                                                         2           2.0000                2018-01-30 00:00:00.0     2           
4351        SAA0208-2    Gelatina                                                         20          2.0000                2018-01-28 00:00:00.0     1           
(3 rows)Execution time: 0.03 seconds