I have a "Transaction" table.
A Transaction can have multiple "TransactionProduct".
A Transaction product can have multiple "PriceModifiers".
I have a query that return every transaction products with the total of price modifiers (reduction amount).
The query :
WHERE TRANSACTIONPRODUCTID = TRANSACTIONPRODUCT.TRANSACTIONPRODUCTID
TRANSACTION.TRANSACTIONID = TRANSACTIONPRODUCT.TRANSACTIONID
SELECT T.DESCRIPTION, TP.PRODUCTNAME, TP.EFFECTIVEPRICE, SUM(PM.AMOUNT) FROM TRANSACTION T JOIN TRANSACTIONPRODUCT TP ON T.TRANSACTIONID = TP.TRANSACTIONID JOIN PRICEMODIFIER PM ON PM.TRANSACTIONPRODUCTID = TP.TRANSACTIONPRODUCTID GROUP BY T.DESCRIPTION, TP.PRODUCTNAME, TP.EFFECTIVEPRICE