CODEWITHSUNDEEP
mysqlsql
wyknzo
wyknzo

Reputation: 81

mysql left join duplicate my table rows - why?

I have 2 tables:

I build this mysql query: 

SELECT SUM(table1.product_quantity) - SUM(order_quantity) AS instocks
  FROM table1  -- table.1 in original
  LEFT JOIN table2 ON table2.product_hashid = table1.product_id
 WHERE table1.product_id = '$thisid'

This query duplicates table2 row with table1. Is there some error in this query?

First, I want to sum all product_quantity from table1 where product_id = '$this' and sum all order_quantity in table2 where product_hashid = '$this' and make (a - b) to display a final result.

Upvotes: 2

Views: 8134

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 754910

Your outline of what you want to do is good, but it isn't what you implemented.

  • I want to sum all product_quantity from table1 where product_id = '$this' and sum all order_quantity in table2 where product_hashid = '$this' and make (a - b) to display a final result.

Build it up one step at a time.

SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID = '$this';

SELECT SUM(order_quantity) FROM Table2 WHERE Product_HashID = '$this';

SELECT (SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID     = '$this') -
       (SELECT SUM(order_quantity)   FROM Table2 WHERE Product_HashID = '$this')
  FROM Dual;

You might observe that the code alignment emphasizes the inconsistent column naming for the product ID columns.

In the more general case:

SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
  FROM Table1
 GROUP BY Product_ID;

SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
  FROM Table2
 GROUP BY Product_HashID;

SELECT p.Product_ID, p.Product_Quantity - o.Order_Quantity AS SurplusOnHand
  FROM (SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
          FROM Table1
         GROUP BY Product_ID) AS P
  JOIN (SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
          FROM Table2
         GROUP BY Product_HashID) AS O
    ON O.Product_ID = P.Product_ID;

Sometimes you need to use a LEFT OUTER JOIN; mostly, you don't. Write your SQL assuming you don't until you're sure that you do.

Given the data cardinalities (row counts), you may need to do an LOJ here. You need to manufacture a zero for the order quantity of those products listed in Table1 that are not listed in Table2.

SELECT    (SELECT SUM(product_quantity) FROM Table1 WHERE Product_ID     = '$this') -
       NVL(SELECT SUM(order_quantity)   FROM Table2 WHERE Product_HashID = '$this'), 0)
  FROM Dual;


SELECT p.Product_ID, p.Product_Quantity - NVL(o.Order_Quantity, 0) AS SurplusOnHand
  FROM (SELECT Product_ID, SUM(product_quantity) AS Product_Quantity
          FROM Table1
         GROUP BY Product_ID) AS P
  LEFT OUTER JOIN
       (SELECT Product_HashID AS Product_ID, SUM(order_quantity) AS Order_Quantity
          FROM Table2
         GROUP BY Product_HashID) AS O
    ON O.Product_ID = P.Product_ID;

Upvotes: 2

IAmTimCorey
IAmTimCorey

Reputation: 16755

I think the issue is your JOIN and your WHERE clause. In this setup, you will return all of the data from table2, which will give you a mess. Your WHERE clause is looking at table1 and limiting the rows there but your JOIN returns all rows from table2 and only those rows from table1 that are equal.

Bottom line: change your join to be an INNER JOIN and your problems will go away.

Upvotes: 0

Graham A
Graham A

Reputation: 71

LEFT JOIN will return all of table1 in every case. http://www.w3schools.com/sql/sql_join_left.asp

Do you actually want just the products in table1 if they appear in table 2? If so, you need simply JOIN (or INNER JOIN) http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 1

Related Questions