Prevent duplicate rows when using LEFT JOIN in Postgres without DISTINCT

Question

I have 4 tables:

• Item
• Purchase
• Purchase Item
• Purchase Discount

In these tables, the Purchase Discount has two entries, all the others have only one entry. But when I query them, due to the LEFT JOIN, I'm getting duplicate entries.

This query will be running in a large database, and I heard using DISTINCT will reduce the performance. Is there any other way I can remove duplicates without using DISTINCT?

Here is the SQL Fiddle.

The result shows:

[{"item_id":1,"purchase_items_ids":[1234,1234],"total_sold":2}]

But the result should come as:

[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1}]

Answer 1

Using correlated subquery instead of LEFT JOIN:

SELECT array_to_json(array_agg(p_values)) FROM 
( 
  SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
    ( 
      SELECT purchase_items.item_id AS item_id,
             ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
             SUM(purchase_items.sold) as total_sold,
             SUM((SELECT SUM(pd.discount_amount) FROM purchase_discounts pd
                  WHERE pd.purchase_id = purchase.id)) as discount_amount
       FROM items
       INNER JOIN purchase_items ON purchase_items.item_id = items.id
       INNER JOIN purchase ON purchase.id = purchase_items.purchase_id
       WHERE purchase.id = 200
       GROUP by purchase_items.item_id
    ) as t 
  INNER JOIN items i ON i.id = t.item_id 
) AS p_values;

db<>fiddle demo

Output:

[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1,"discount_amount":12}]