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...

I would be very grateful for the help!
June 3rd 19 at 19:12
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