How resolve inner join query issue ?

Question

Hi ,

The bellow query working as expected , until include one more table in join .

Working query :

SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,'Inventory Adjustment' AS RefType,CONCAT('IN ',trim(A.Notes)) AS RefNo,
AdjustQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck
FROM da1.InventoryAdjust A
Inner Join da1.Inventory IV On A.InventoryID=IV.InventoryID

WHERE AdjustQuantity>0
and (Date(DateAdjusted) between '2025-03-07' and '2025-03-07')
Order By A.InventoryID ;

***************************************************************************

Sample Record :

4, 12, 'A02 ', 'The OG', '2025-03-07 10:20:57', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 13:16:39', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
5, 12, 'A03 ', 'Popeye`s Bowl', '2025-03-07 10:22:42', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0

after i include New table in inner join : inventorycostaveragedetail

SELECT A.InventoryID,IV.CategoryID,Barcode,IV.Description,DateAdjusted AS RefDate,'Inventory Adjustment' AS RefType,CONCAT('IN ',trim(A.Notes)) AS RefNo,
AdjustQuantity As StockInQty, 0.00 AS StockOutQty, 0 As StockCheck
FROM da1.InventoryAdjust A
Inner Join da1.Inventory IV On A.InventoryID=IV.InventoryID
join da1.inventorycostaveragedetail as ic on ic.Inventoryid=A.Inventoryid
WHERE AdjustQuantity>0
and (Date(DateAdjusted) between '2025-03-07' and '2025-03-07')
Order By A.InventoryID ;

Result :

4, 12, 'A02 ', 'The OG', '2025-03-07 13:16:39', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 10:20:57', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 13:16:39', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 10:20:57', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 13:16:39', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
4, 12, 'A02 ', 'The OG', '2025-03-07 10:20:57', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
5, 12, 'A03 ', 'Popeye`s Bowl', '2025-03-07 10:22:42', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
5, 12, 'A03 ', 'Popeye`s Bowl', '2025-03-07 10:22:42', 'Inventory Adjustment', 'IN Adj In', 10, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0
26, 6, 'C01', '01. KOPI O', '2025-03-07 10:19:45', 'Inventory Adjustment', 'IN Adj In', 5, 0.00, 0

The Problem is data is keep repeating , the resutl should be 4 rows instead of 14 rows . Cna any one help me to change this quey.

Thanks in advance

Karthik.K

Tuhin Paul · Answer

Approach 2 If you need data from inventorycostaveragedetail , ensure the join condition uniquely links rows. For example, if inventorycostaveragedetail has a timestamp or transaction ID: SELECT A.InventoryID, IV.CategoryID, Barcode, IV.Description, DateAdjusted AS RefDate, 'Inventory Adjustment' AS RefType, CONCAT('IN ', TRIM(A.Notes)) AS RefNo, AdjustQuantity AS StockInQty, 0.00 AS StockOutQty, 0 AS StockCheck FROM da1.InventoryAdjust A INNER JOIN da1.Inventory IV ON A.InventoryID = IV.InventoryID INNER JOIN da1.inventorycostaveragedetail ic ON A.InventoryID = ic.InventoryID AND A.DateAdjusted = ic.TransactionDate -- Add a specific condition WHERE AdjustQuantity > 0 AND DATE(DateAdjusted) BETWEEN '2025-03-07' AND '2025-03-07' ORDER BY A.InventoryID; Approach 3 If you need one row per InventoryID from inventorycostaveragedetail , use a subquery: SELECT A.InventoryID, IV.CategoryID, Barcode, IV.Description, DateAdjusted AS RefDate, 'Inventory Adjustment' AS RefType, CONCAT('IN ', TRIM(A.Notes)) AS RefNo, AdjustQuantity AS StockInQty, 0.00 AS StockOutQty, 0 AS StockCheck FROM da1.InventoryAdjust A INNER JOIN da1.Inventory IV ON A.InventoryID = IV.InventoryID INNER JOIN ( SELECT InventoryID, MAX(CostDate) AS LatestDate FROM da1.inventorycostaveragedetail GROUP BY InventoryID ) ic ON A.InventoryID = ic.InventoryID WHERE AdjustQuantity > 0 AND DATE(DateAdjusted) BETWEEN '2025-03-07' AND '2025-03-07' ORDER BY A.InventoryID;

Tuhin Paul · Answer

Approach 1 The issue arises because joining the inventorycostaveragedetail table introduces multiple matching rows for each InventoryID , leading to duplicates. If you don't need data from inventorycostaveragedetail , simply remove the join: SELECT A.InventoryID, IV.CategoryID, Barcode, IV.Description, DateAdjusted AS RefDate, 'Inventory Adjustment' AS RefType, CONCAT('IN ', TRIM(A.Notes)) AS RefNo, AdjustQuantity AS StockInQty, 0.00 AS StockOutQty, 0 AS StockCheck FROM da1.InventoryAdjust A INNER JOIN da1.Inventory IV ON A.InventoryID = IV.InventoryID WHERE AdjustQuantity > 0 AND DATE(DateAdjusted) BETWEEN '2025-03-07' AND '2025-03-07' ORDER BY A.InventoryID;

Sophia Carter · Answer

It looks like you are encountering a data duplication issue when you include the table "inventorycostaveragedetail" in your query. To address this problem and ensure that the result set contains the expected 4 rows instead of 14 rows, you need to refine your join conditions. Here are a few steps you can take to modify your query and avoid the data repetition: 1. Review the Join Conditions: - Double-check the join conditions between tables "InventoryAdjust" and "InventoryCostAverageDetail" to ensure that they are correctly matching records. You may need to adjust the join criteria to eliminate duplicate matches. 2. Use Distinct Keyword: - Consider using the DISTINCT keyword in your query to remove duplicate rows from the result set. This can help filter out repeated records and return only unique rows. 3. Refine the WHERE Clause: - Review the WHERE clause to make sure it is not inadvertently causing the duplication issue. Ensure that the conditions accurately filter the data without causing unintended replication. 4. Validate Data Matching: - Verify that the columns used for joining the tables have unique values in each table. If there are multiple matching rows, it can lead to duplication when joining tables. Here's an example of how you can include the DISTINCT keyword in your query to address the data repetition: SELECT DISTINCT A.InventoryID, IV.CategoryID, Barcode, IV.Description, DateAdjusted AS RefDate, 'Inventory Adjustment' AS RefType, CONCAT('IN ', TRIM(A.Notes)) AS RefNo, AdjustQuantity AS StockInQty, 0.00 AS StockOutQty, 0 AS StockCheck FROM da1.InventoryAdjust A INNER JOIN da1.Inventory IV ON A.InventoryID = IV.InventoryID JOIN da1.InventoryCostAverageDetail AS ic ON ic.Inventoryid = A.Inventoryid WHERE AdjustQuantity > 0 AND (DATE(DateAdjusted) BETWEEN '2025-03-07' AND '2025-03-07') ORDER BY A.InventoryID; By incorporating the DISTINCT keyword, you should be able to eliminate duplicate rows in the result set. Make sure to adapt the query according to the specific column names and data relationships in your database schema. Give this a try and see if it resolves the data repetition issue in your query. Let me know if you require further assistance or if you encounter any other challenges. Good luck with refining your query!