SQL remove all duplicates from query with inner join (3 tables together)

Question

I'm trying to create query with inner join (3 tables altogether) to display only one result per item (in this case it will be MATERIAL_ID).

I have created this query:

SELECT DISTINCT
A.ORDER_ID, A.BATCH_ID, A.MATERIAL_ID, B.MATERIAL_DESC, A.TARGET_QTY, A.DISPENSED_QTY,     A.REMAINING_QTY, A.DISPENSED_UOM, A.DISPENSE_STATUS, B.MATERIAL_TYPE, A.UNIT_PROCEDURE_ID, A.SPLIT_ID, A.BOM_REF_NO, C.CONTAINER_STATUS, C.AREA_ID, C.QTY_STATUS, C.EXPIRE_DATE 
FROM
MM_DISP_MATL_ST A
INNER JOIN 
MM_MATERIAL_SP B
ON A.MATERIAL_ID = B.MATERIAL_ID
INNER JOIN
MM_CONTAINER_ST C
ON B.MATERIAL_ID = C.MATERIAL_ID
AND C.CONTAINER_STATUS = 'Unrestricted' 
AND C.QTY_STATUS IN ('Full','Partial')
WHERE A.ORDER_ID = :pORDER_NUMBER
ORDER BY A.BOM_REF_NO

but it gives me all info with duplicates for MATERIAL_ID, AREA_ID AND EXPIRE_DATE.

How can I modify this query to show me only 1 result per MATERIAL_ID or BOM_REF_NO and also to select earliest EXPIRE_DATE?

I tried DISTINCT but gives me duplicates which I don't want. Also adding extra ORDER BY didn't help

Answer 1

You could just say in the where clause that you want the record with the earliest expire date

... 
WHERE A.ORDER_ID = :pORDER_NUMBER
  AND  C.EXPIRE_DATE = (SELECT MIN(EXPIRE_DATE) FROM MM_CONTAINER_ST C2
                         WHERE B.MATERIAL_ID = C2.MATERIAL_ID)
ORDER BY A.BOM_REF_NO`