Why are multiple joins resulting in duplicated data

nighthawk 0
Viewed 42

We have four tables: Product, SpecialOffers, SpecialOfferProducts and ProductPurchases. Fidddle is here and here is my question.

Products:

Id Name Price
1 Product A 10
2 Product B 20
3 Product C 30
4 Product D 40
5 Product E 50

SpecialOffers:

Id Name
1 Offer 1
2 Offer 2
3 Offer 3

SpecialOfferProducts (products included in offers):

Id SpecialOfferId ProductId
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 4

ProductsPurchases (number of purchases of each product, linked to offer):

Id SpecialOfferId ProductId Quantity
1 1 1 1
2 1 1 1
3 1 1 1
4 2 2 1
5 2 2 1
6 3 3 1

So, we have 4 products, three special offers. Product can be offered in zero, one or more special offers. In this case, Offer 1 contains products A, B, C, Offer 2 contains products A and B and Offer 3 has only product D. Product E is not in any offer. Also, sales are tracked in table ProductPurchases. In fiddle, I've tried to write a query to return a list of products containing both offer and purchase data in JSON format as columns. Problem is, result (incorrectly) outputs that product A is in six offers and purchases, product B in four offers and purchases and the rest of products are ok - products A and B's offers and purchases are doubled. Query is like this:

SELECT P.*, JSON_AGG(JSON_BUILD_OBJECT('offer.id', SO.ID, 'offer.name', SO.NAME)) AS OFFERS, JSON_AGG(JSON_BUILD_OBJECT('purchase.id', PP.ID, 'purchase.specialOfferId', PP.SPECIAL_OFFER, 'purchase.specialOfferName', SO.NAME)) AS PURCHASES
FROM PRODUCTS P
LEFT JOIN SPECIAL_OFFER_PRODUCTS SOP ON SOP.PRODUCT_ID = P.ID
LEFT JOIN SPECIAL_OFFERS SO ON SO.ID = SOP.SPECIAL_OFFER_ID
LEFT JOIN PRODUCT_PURCHASE PP ON PP.PRODUCT_ID = P.ID
GROUP BY P.ID

What I want is list of products with all product columns plus two columns containing JSON with purchases of that product (so A has 3, B has 2, C has one and rest have zero) and special offers they are included in (same idea as purchases).

Apparently, the number of offers and purchases is double the number of purchases - if we add additional purchase so there is four of them (instead of three as in example), number of reported offers and purchases will be 8. Why is this happening?

sql postgresql
1 Answers

That is happening because of your left joins. Try this one:

select p.*, so.offers, sop.PURCHASES
from products p,
     lateral (select JSON_AGG(JSON_BUILD_OBJECT('offer.id', SO.ID, 'offer.name', SO.NAME))
              from specialoffers so
                       inner join specialofferproducts sop on sop.specialofferid = so.id
              where sop.productid = p.id) as so(offers),
     lateral (select JSON_AGG(JSON_BUILD_OBJECT('purchase.id', PP.ID, 'purchase.specialOfferId', PP.SpecialOfferId,
                                                'purchase.specialOfferName', SO.NAME) order by pp.id)
              from ProductsPurchases PP
                       inner join specialoffers so on pp.specialofferid = so.id
              where PP.ProductId = P.id ) AS sop(PURCHASES);

DBFiddle demo

EDIT: Result:

id name price offers purchases
1 Product A 10 [{"offer.id" : 1, "offer.name" : "Offer 1"}, {"offer.id" : 2, "offer.name" : "Offer 2"}] [{"purchase.id" : 1, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}, {"purchase.id" : 2, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}, {"purchase.id" : 3, "purchase.specialOfferId" : 1, "purchase.specialOfferName" : "Offer 1"}]
2 Product B 20 [{"offer.id" : 1, "offer.name" : "Offer 1"}, {"offer.id" : 2, "offer.name" : "Offer 2"}] [{"purchase.id" : 4, "purchase.specialOfferId" : 2, "purchase.specialOfferName" : "Offer 2"}, {"purchase.id" : 5, "purchase.specialOfferId" : 2, "purchase.specialOfferName" : "Offer 2"}]
3 Product C 30 [{"offer.id" : 1, "offer.name" : "Offer 1"}] [{"purchase.id" : 6, "purchase.specialOfferId" : 3, "purchase.specialOfferName" : "Offer 3"}]
4 Product D 40 [{"offer.id" : 3, "offer.name" : "Offer 3"}] null
5 Product E 50 null null
Cetin Basoz0
Related