How to create view from two tables?

Good day experts!)

I can not understand how to create a view from two tables...

The point is very simple:
1. There is a table which shows the quantity of the ordered goods from the supplier
2. And there is a table that shows how many goods you have bought

Here you need to make a view that will show the balance in stock (i.e. ordered minus purchased)

As seen in the picture, the item with "id_товара=15" had not yet been purchased, but still displayed in view of 25...
5b54052f2c1ea094570831.png

I would be very grateful for the help!
June 3rd 19 at 19:12
2 answers
June 3rd 19 at 19:14
Solution
A single view there is unlikely to do-you need to make a subquery, like this:
CREATE VIEW `sold_quantity` AS
 SELECT `sold`.`id`, SUM( `sold`.`quantity`) AS `sold_quantity`
 FROM `sold`
 GROUP BY `sold`.`id`;

CREATE VIEW `remains` AS
SELECT
`ordered`.`id`,
 ( SUM( `ordered`.`quantity` ) - IFNULL( `sold_quantity`.`sold_quantity`, 0 ) ) AS `remains`
 FROM `ordered`
 LEFT JOIN `sold_quantity` ON `sold_quantity`.`id` = `ordered`.`id`
 GROUP BY `ordered`.`id`;

SELECT * FROM `remains`;
5 8
10 12
13 18
15 25
ie id_товара=15 is not displayed? - Everardo commented on June 3rd 19 at 19:17
no , but if you want to output, will have to improve a little bit the queries. - Arthur_Hudson99 commented on June 3rd 19 at 19:20
If you do not complicate, help please - Everardo commented on June 3rd 19 at 19:23
corrected the answer. - Arthur_Hudson99 commented on June 3rd 19 at 19:26
Thank you!! He would be a long time before it was... - Everardo commented on June 3rd 19 at 19:29
June 3rd 19 at 19:16
Solution
select
t1.id
t1.sum_ordered - ifnull(t2.sum_sold, 0)
from (select id, sum(amount) as sum_ordered from ordered group by id) t1
left join (select id, sum(amount) as sum_sold from sold group by id) t2 on t2.id = t1.id


sqlfiddle.com/#!9/4141ef/6/0

Find more questions by tags MySQL