Understanding SELECT Statements Using Parent and Child Tables

About the image above
The parent table is m_product
The child table is t_sales.


SELECT m_product.product_code,
 m_product.product_name,
 m_product.price,
 SUM(quantity) as total,
 SUM(quantity)* price as result 
FROM m_product, t_sales
WHEREm_product.product_code=t_sales.product_code
GROUP BYm_product.product_code;

What do you want to do
If you use the SQL statement above, it looks like the image below.
I would like to display soy sauce ramen under salt ramen.
Do not change the contents of the parent and child tables.
You can change the Create Table statement.

Contents of soy sauce ramen
003
in product_code Product_name and soy sauce ramen
I would like to see null or 0 for other price total results.

Thank you for your cooperation.

mysql

2022-09-30 21:59

1 Answers

Use left external bond to display records that cannot be joined by an internal bond.

SQL statement

SELECT m_product.product_code,
 m_product.product_name,
 m_product.price,
 SUM(quantity) as total,
 SUM(quantity)* price as result 
FROM m_product left join_sales on m_product.product_code=t_sales.product_code
GROUP BYm_product.product_code;

Results

DDL statements, insert statements

create table m_product(product_code char(3), product_name varchar(20), price int);
insert into_product values ('001', 'Miso Ramen', 100); 
insert into_product values ('002', 'Salt Ramen', 1000);
insert into_product values ('003', 'Soy sauce ramen', 1000);

create table_sales(product_code char(3), quality int);
insert into_sales values ('001', 10);
insert into_sales values ('002', 0);

It's hard to answer with images on the table, so it might be easier to get answers with text.
SQL Fiddle

2022-09-30 21:59

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656