Joining two tables in PHP MySQL is not working-CodePudding

I have two tables: purchase and sales. I need to make auto summary of total purchase and sales group wise in third table with remaining stock. Following is my code but its only summing the sale matching id with purchase table. But I want to sum of all purchase and sales.

Product     Purchase    Sale    Balance
OPPO A26    34           1        33

SELECT p.product_name,
       coalesce(SUM(p.product_qty),0) as pqty,
       coalesce(SUM(s.product_qty),0) as sqty,
       coalesce(SUM(p.product_qty),0) - coalesce(SUM(s.product_qty),0) as balance
            
FROM purchase p 
LEFT JOIN sale s 
ON s.id = p.id
GROUP BY p.product_name

CodePudding user response：

I'm guessing you're joining the wrong value:

LEFT JOIN sale s ON s.id = p.id
                    ^^^^^^^^^^^

Unless the auto_increment of your Sale and Product in in absolute sync, those wont join (or weirdly join). It should likely be something like:

LEFT JOIN sale s ON s.product_id = p.id

CodePudding user response：

SELECT p.product_name, coalesce(SUM(p.product_qty),0) as pqty,          coalesce(SUM(s.product_qty),0) as sqty,coalesce(SUM(p.product_qty),0) coalesce(SUM(s.product_qty),0) as balance 
FROM purchase p 
LEFT JOIN sale s ON p.id = s.id 
GROUP BY p.product_name

i think the table which is using in join comes after the =