Retrieve items by latest date, item code and comparison to additional table-CodePudding

I have two tables:

table "A" with various items identified by item codes (integer). each item appears several times, with different upload dates. the tables also show the store under which the item is sold (store ID- integer)

table "B" with a list of the desired item codes (50 items) to draw.

I am interested in extracting (showing) items from the first table, according to the item codes on the second table. the items chosen should also have the highest upload date and belong to a specific store id (as I choose).

for example: the item "rice", has an item code of - 77893. this item code is on table "B", meaning I want to show it. in table "A", there are multiple entries for "rice":

table exapmle

table "A":

item_name | item_code | upload_date | store_id

rice | 77893 | 2021-11-18 | 001

rice | 77893 | 2020-05-30 | 011

rice | 77893 | 2020-11-02 | 002

apple | 90837 | 2020-05-14 | 002

apple | 90837 | 2020-05-14 | 020

rice | 77893 | 2020-05-15 | 002

apple | 90837 | 2020-01-08 | 002

rice | 77893 | 2020-05-15 | 005

table "B":

item_code

90837

77893

output:

item_name | item_code | upload_date | store_id

rice | 77893 | 2020-11-02 | 002

apple | 90837 | 2020-05-14 | 002

"rice" and "apple" have item codes that are also on table "B". in this example, I am interested in items that are sold at store 002.

so far I only managed to return the item by its latest upload date. however, I inserted the item code manually and also was not able to filter store_id's.

any help or guidelines on how to execute this idea will be very helpful.

thank you!

CodePudding user response：

Filter the rows of the table A for the store that you want and the items that you have in the table B and then aggregate to get the rows with the max date:

SELECT item_name, MAX(upload_date) upload_date, store_id
FROM A 
WHERE store_id = '002' AND item_name IN (SELECT item_name FROM B)
GROUP BY item_code;

or, with a join:

SELECT A.item_name, MAX(A.upload_date) upload_date, A.store_id
FROM A INNER JOIN B
ON B.item_code = A.item_code
WHERE A.store_id = '002'
GROUP BY A.item_code;

See the demo.