Duplicate records in table after left join-CodePudding

I have three tables.

"Departments" table: department_name, department_id.

Table "LowPrice": departmend_id, product_name, purchased, sold, price.

Table "Basic": departmend_id, product_name, purchased, sold, price.

In the tables "Basic" and "Low" there can be several records pertaining to one department.

There is no relations between these tables. I want to develop a query that will group records from the Low and Basic tables by the department number from the Departments table. When grouping, the cost of sold and purchased goods from the "Basic" and "Low price" tables for each department is summed up.

My query code:

    SELECT Departments.departmend_id, 
    Sum([Basic]![purchased] [Basic]![price]) AS PurchasedBasic 
    Sum([Basic]![sold] [Basic]![price]) AS SoldBasic, 
    Sum([Basic]![purchased] [Basic]![price]) - Sum([Basic]![sold][Basic]![price]) AS DiffBasic, 
    Sum([LowPrice]![purchased] [LowPrice]![price]) AS PurchasedLowPrice, 
    Sum([LowPrice]![sold] [LowPrice]![price]) AS SoldLowPrice, 
    Sum([LowPrice]![purchased] [LowPrice]![price]) - Sum([LowPrice]![sold] [LowPrice]![price]) AS DiffLowPrice
    FROM (Departments LEFT JOIN Basic ON Departments.departmend_id = Basic.departmend_id) LEFT JOIN LowPrice ON Departments.departmend_id = LowPrice.departmend_id
    GROUPBY Departments.departmend_id;

But now only those departments for which there are records in only one table are counted correctly. If there are records for a department in two tables, then their number is multiplied and the query gives an incorrect result.

Please enlighten me what I need to change in my query.

CodePudding user response：

Consider combining LowPrice and BasicPrice into one table since qualitatively they contain the same information. Simply, add a Type field to designate low price and basic price records.

Make-Table Query (to be run once)

SELECT p.*
INTO Prices
FROM
   (SELECT b.department_id,
           b.product_name,
           b.purchased,
           b.sold, 
           b.price,
           'Basic Price' AS type
    FROM BasicPrice b

    UNION ALL

    SELECT l.department_id,
           l.product_name,
           l.purchased,
           l.sold, 
           l.price,
           'Low Price' AS type
    FROM LowPrice l
   ) p

Once your properly normalized those two relations for more efficient storage, run conditional aggregation. Below uses table aliases and calculated columns which is supported in MS Access.

Conditional Aggregation Query

SELECT d.departmend_id, 
       d.department_name,
       SUM(IIF(p.Type = 'Basic Price', p.[purchased] * p.[price], NULL)) AS PurchasedBasic,
       SUM(IIF(p.Type = 'Basic Price', p.[sold] * p.[price], NULL)) AS SoldBasic, 
       [PurchasedBasic] - [SoldBasic] AS DiffBasic, 

       SUM(IIF(p.Type = 'Low Price', p.[purchased] * p.[price], NULL)) AS PurchasedLowPrice, 
       SUM(IIF(p.Type = 'Low Price', p.[sold] * p.[price], NULL)) AS SoldLowPrice, 
       [PurchasedLowPrice] - [SoldLowPrice] AS DiffLowPrice

FROM Departments d
LEFT JOIN Prices p ON d.departmend_id = p.departmend_id

GROUP BY d.departmend_id,
         d.department_name;