Query Multiplies The Result When Selecting For One Value

Question

Hello, I want to show Product, Sub Materials,Alternatives, Orders and Dates. Product has 5 sub materials and 8 orders in different dates. The problem I am facing is that Sub Materials multiplies 8 times according to orders. How can I fetch data properly for one repeated value? Thank you for your support.

-Query is as follows

SELECT 
      B.BOMREC_CODE AS PRODUCT_CODE,
      S.NAME AS PRODUCT_NAME,
      B.BOMREC_MATERIALCODE AS SUB_MATERIAL,
      S1.NAME AS SUB_MATERIAL_NAME,
      B.BOMREC_ALTCODE AS ALTERNATIVE,
      (SIP.SF_QUANTITY - SIP.SF_NEWSHIPPED) AS OPEN_ORDERQT
      SIP.DLVDATE AS DELIERY_DATE
FROM BOMU01T B
LEFT JOIN STOCK00 S ON TRIM(S.CODE)=TRIM(B.BOMREC_CODE) 
LEFT JOIN STOCK00 S1 ON TRIM(S1.CODE)=TRIM(B.BOMREC_MATERIALCODE)
LEFT JOIN STOCK40T SIP ON TRIM(SIP.CODE)=TRIM(B.BOMREC_CODE)
WHERE B.BOMREC_CODE='2496802300'
ORDER BY 1 DESC

-STOCK00 Table holds the Product's name

-STOCK40T Table holds the orders. When I search the orders for '2496802300' it shows as below

-What I want should look like this

Answer 1

Looks like your missing a Group by clause

Try This:

SELECT 
      B.BOMREC_CODE AS PRODUCT_CODE,
      S.NAME AS PRODUCT_NAME,
      B.BOMREC_MATERIALCODE AS SUB_MATERIAL,
      S.NAME AS SUB_MATERIAL_NAME,
      B.BOMREC_ALTCODE AS ALTERNATIVE,
      (SIP.SF_QUANTITY - SIP.SF_NEWSHIPPED) AS OPEN_ORDERQT
      SIP.DLVDATE AS DELIERY_DATE
FROM BOMU01T B
LEFT JOIN STOCK00 S ON TRIM(B.BOMREC_CODE)= TRIM(S.CODE) 
AND TRIM(B.BOMREC_MATERIALCODE)=TRIM(S.CODE)
LEFT JOIN STOCK40T SIP ON TRIM(B.BOMREC_CODE)=TRIM(SIP.CODE)
WHERE B.BOMREC_CODE='2496802300'   

ORDER BY 7 DESC;