Vicheanak Vicheanak - 3 months ago 8
MySQL Question

How to SELECT Non Exist Row in MySql?

I have 2 tables:

enter image description here

And here is the data:

Products



enter image description here

Orders



enter image description here

I want to have a query that produces this result:

enter image description here

I have tried this:

SELECT p.id, p.name, sum(o.amount) AS total
FROM products AS p
INNER JOIN orders AS o ON o.product_id = p.id
GROUP BY p.id


But I get this:

enter image description here

How can I get the result as expected?

swe swe
Answer

You do have to do a left outer join like that:

SELECT p.id, p.name, IFNULL(sum(o.amount), 0) AS total
    FROM products AS p 
    LEFT OUTER JOIN orders AS o ON o.product_id = p.id 
    GROUP BY p.id

the [OUTER]-Keyword is optional and can be left out.

Have a look at this question: MySQL: Typecasting NULL to 0

But sorry, this really is so basic, you can google it all around the world in every language...