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