Query did not return all rows

Question

setup:

create table product_stock(product_id integer, qty integer, branch_id integer);

create table product(product_id integer, product_name varchar(255));

insert into product(product_id, product_name) 
       values(1, 'Apsana White DX Pencil');

insert into product(product_id, product_name) 
       values(2, 'Diamond Glass Marking Pencil');

insert into product(product_id, product_name) 
       values(3, 'Apsana Black Pencil');

insert into product_stock(product_id, qty, branch_id) 
       values(1, 100, 1);

insert into product_stock(product_id, qty, branch_id) 
       values(1, 50, 2);

insert into product_stock(product_id, qty, branch_id) 
       values(2, 80, 1);

my query:

SELECT IFNULL(SUM(s.qty),0) AS stock, 
       product_name 
FROM product_stock s 
 RIGHT JOIN product p ON s.product_id=p.product_id
WHERE branch_id=1 
GROUP BY product_name 
ORDER BY product_name;

returns:

+-------+-------------------------------+ 
| stock | product_name                  | 
+-------+-------------------------------+ 
| 100   | Apsana White DX Pencil        | 
|  80   | Diamond Glass Marking Pencil  | 
+-------+-------------------------------+ 
1 row in set (0.00 sec)

But I want to have the following result:

+-------+------------------------------+ 
| stock | product_name                 | 
+-------+------------------------------+ 
|   0   | Apsana Black Pencil          | 
| 100   | Apsana White DX Pencil       | 
|  80   | Diamond Glass Marking Pencil | 
+-------+------------------------------+

To get this result what mysql query should I run?

Answer 1

Try:

   SELECT IFNULL(SUM(s.qty),0) AS stock, 
          product_name 
   FROM product_stock s 
    RIGHT JOIN product p ON s.product_id=p.product_id AND branch_id=1 
   GROUP BY product_name 
   ORDER BY product_name;

Your current query is filtering out the products with no matching product_stock in the WHERE clause, effectively turning the outer join back into an inner join.