Filter multiple id values in MySQL database with Navicat-CodePudding

I need to filter the data with the nearest exp-date and selling-price, without repeating id_product, I try to resolve this problem, but I can't get a proper way to doit

this is Navicat query and result

SELECT
    product_exp_date.idproduct, 
    product_exp_date.exp_date, 
    product_exp_date.selling_price
FROM
    product
    INNER JOIN
    product_exp_date
    ON 
        product.idproduct = product_exp_date.idproduct
GROUP BY
    product_exp_date.exp_date

idproduct  exp_date   selling_price
8         2022-11-01      300
5         2022-06-08      370
5         2022-06-09      350
7         2022-07-01      380
5         2022-09-20      450
6         2022-10-08      140
6         2023-06-08      150

I already tried this way

GROUP BY
    product_exp_date.idproduct

but it's given me different result

idproduct  exp_date   selling_price
5         2022-06-09    350
6         2023-06-08    150
7         2022-07-01    380
8         2022-11-01    300

but I need to get this result

idproduct  exp_date   selling_price
5         2022-06-08      370
6         2022-10-08      140
7         2022-07-01      380
8         2022-11-01      300

PRODUCT TABLE

productid  product_name 
5               A     
6               B     
7               C     
8               D

PRODUCT_EXP_DATE TABLE

idproduct_exp_date  idproduct   exp_date   selling_price
      1               5        2022-06-09    350
      2               6        2023-06-08    150
      3               5        2022-06-08    370
      4               5        2022-09-20    450
      5               6        2022-10-08    140
      6               7        2022-07-01    380
      7               8        2022-11-01    300

sometimes's my query has some error, anyway I need help to resolve this problem, Thank you.

CodePudding user response：

First of all, let me correct you; that is not a Navicat query, that's a MySQL query. Now, that's two different thing. MySQL is a database and Navicat is a tool - equivalent to other tools like MySQL Workbench, PHPMyAdmin or SQLyog. It is made so that you can do database functions through GUI.

Next is, I'm going to give two queries that you can use depending on your MySQL version. The first one is this:

SELECT p1.idproduct,
       p1.exp_date,
       p1.selling_price
FROM product_exp_date p1 
JOIN (
   SELECT idproduct,
       MIN(exp_date) AS minexp
   FROM product_exp_date
   GROUP BY idproduct
  ) AS p2
 ON p1.idproduct=p2.idproduct 
 AND p1.exp_date=p2.minexp
ORDER BY p1.idproduct;

You should be able to run the query above in any version of MySQL or MariaDB. The idea is to get the smallest exp_date group by idproduct, then make it as a sub-query, join it with product table again to match those two extracted value so that we can extract the selling_price.

The second query:

SELECT idproduct,
       exp_date,
       selling_price
FROM (
   SELECT idproduct,
          exp_date,
          selling_price,
          ROW_NUMBER() OVER (PARTITION BY idproduct ORDER BY exp_date) rn
   FROM product_exp_date
  ) AS p1
WHERE rn=1;

This can only run on MySQL v8 or MariaDB 10.2 (and above) that support window function. The idea is a little different from the previous query whereby here, we'll focus on generating the ROW_NUMBER() based on specific conditions, then make that as a sub-query and only add a WHERE. Compared to the query before, this doesn't require JOIN.

As you can see, I did not take the product table into consideration since I don't see it being use anywhere in your original query, but if you do require to join it, and you can't figure out how, just drop me a comment and I'll see what I can do.

Demo fiddle