CODEWITHSUNDEEP
mysql
Crazy
Crazy

Reputation: 867

How to avoid Left join table show duplicate row?

I have some problem with the query issue when trying to sum up the quantity.

Table

This cart item table stored id_cart and id product

enter image description here

This order table stored id_cart and other id may be included such as supplier. This table is used to track order record and send notification to supplier.

enter image description here

Wrong result. Expected output = 1, 1, 1

enter image description here

SELECT  id, id_product, SUM(qty) 
from cart_item 
left join Orderp using(id_cart) 
group by id_product

http://sqlfiddle.com/#!9/07bf57/1

The issue caused by duplicate id_cart in order table as well. How can i handle this? Any solution to make it works? Thanks.

Upvotes: 0

Views: 47

Answers (2)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following query

SELECT
  i.id_product,
  p.name productname,
  b.id_branch,
  b.branchname,
  SUM(i.qty)
from cart_item i
left join (SELECT DISTINCT id_cart,id_branch FROM Orderp) o on o.id_cart=i.id_cart
left join product p on i.id_product=p.id_product
left join catalog c on c.id_product=p.id_product and c.id_branch=o.id_branch
left join branch b on b.id_branch=o.id_branch
group by
  i.id_product,
  p.name,
  b.id_branch,
  b.branchname

The main problem in Orderp table because it containts two different orders for one cart (DISTINCT id_cart,id_branch helps here). And you need to use the second condition by id_branch for catalog (and c.id_branch=o.id_branch).

SQL Fiddle - http://sqlfiddle.com/#!9/f32d5f/16

And I think you can use everywhere INNER JOIN instead LEFT JOIN

SELECT
  i.id_product,
  p.name productname,
  b.id_branch,
  b.branchname,
  SUM(i.qty)
from cart_item i
join (SELECT DISTINCT id_cart,id_branch FROM Orderp) o on o.id_cart=i.id_cart
join product p on i.id_product=p.id_product
join catalog c on c.id_product=p.id_product and c.id_branch=o.id_branch
join branch b on b.id_branch=o.id_branch
group by
  i.id_product,
  p.name,
  b.id_branch,
  b.branchname

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

There is something wrong in your data, or in your data model

INSERT INTO OrderP(`id_order`,`id_cart`)VALUES(1, 1);
INSERT INTO OrderP(`id_order`,`id_cart`)VALUES(2, 1);

There are 2 rows for id_cart = 1, so the "natural join" will double every row when joining cart_item to orderp.

Using an inner join to a different column in orderp works better because now there is only one row in orederp for each cart_item.

SELECT id_product, sum(qty)
from cart_item ci
left join Orderp o on ci.id_cart = o.id_order
GROUP BY id_product

http://sqlfiddle.com/#!9/07bf57/13

Upvotes: 1

Related Questions