why are articles in 0's or null not shown in my final mysql report?

I appeal to you since I have not been able to resolve this detail in the sql statement, well, the error I have is when I consult my products classified by category, because when I filter with a simple where I get 126 products, but when I include the joins with the other tables of inputs, outputs and initial inventory only shows me 49, I have already tried to do an if / case to put 0's if it is the case that there is no record of that product in entries and departures.

In conclusion to make the query only throws me those that do have inputs and outputs.

I would like to know how to make the changes to the statement so that you can show me all the products still only having initial inventory.

Thank you.

My statement sql:

SELECT art.id as Id,
art.name as Producto,
linea.name as 'Categoria',
art.measure as 'U. Medida',
war.quantity as 'Total Inicial',
(sum(pur.quantity)) as 'Total Entradas',
sal.salidas as 'Total Salidas',
(sum(pur.quantity) + (war.quantity) )as 'Total Global',
(sum(pur.quantity) + (war.quantity) )- sal.salidas as Disponible 
from articles art 
    inner join warehouses_details war on war.article_id=art.id 
    inner join asd.lines linea on linea.id=art.line_id 
    inner join purchases_details pur on pur.article_id=art.id 
    inner join (SELECT sales_details.article_id,SUM(sales_details.quantity) AS salidas FROM sales_details group by sales_details.article_id) sal on sal.article_id= art.id 
where pur.purchase_id is not null and art.line_id='$categoria' 
group by art.id order by art.id ASC 

Update:

From the product table I need these fields:

id first name category unit of measure

of the category table:

id category name

of the shopping_detail table

id product amount (will be added) shopping id

of the sales_detail table

id product amount (will be added)

of the table almacen_detalle id product amount (will be added)

I am reformulating the sentence with left join.

Update 2:

In effect with the left join it shows me the results as I want, I have not yet implemented the other tables, but for the moment it has thrown all the results.

Here I put the sentence sql:

select art.id, art.name as producto, cat.name as categoria, pur.total from articles art
inner join
asd.lines cat
on
cat.id = art.line_id
left join
(select purchases_details.article_id as prod, purchases_details.purchase_id as compra,
sum(purchases_details.quantity) as total from purchases_details where purchases_details.purchase_id is not null group by purchases_details.article_id)pur
on
pur.prod=art.id 
where cat.id=5 group by art.id;

here the result:

Update 3:

I already made the implementation of the left join and also converted the nulls to 0's that was what I needed, thanks for the help, I leave you the sentence and the evidence.

select art.id, art.name as producto,art.measure as 'U. Med', cat.name as categoria, coalesce(pur.total,0) as entradas from articles art
left join
lines cat
on
cat.id = art.line_id
left join
(select purchases_details.article_id as prod, purchases_details.purchase_id as compra,
sum(purchases_details.quantity) as total 
from purchases_details where purchases_details.purchase_id is not null group by purchases_details.article_id)pur
on
pur.prod=art.id
where cat.id=5 group by art.id;

The result:

Final:

I solved all the problems, thank you, here I put the code for whoever occupies it, I do not know if it is well optimized, but it works for me: [/ p]

select art.id, 
art.name as producto,art.measure as 'U. Med', 
cat.name as categoria,
coalesce(war.inicial,0) as Inicial, 
coalesce(pur.compras,0) as Entradas, 
coalesce(sal.salidas,0) as Salidas ,
coalesce(sum(coalesce(war.inicial,0)+coalesce(pur.compras,0)),0) as 'General',
coalesce(coalesce(sum(coalesce(war.inicial,0)+coalesce(pur.compras,0)),0)-coalesce(sal.salidas,0),0) as Disponible
from articles art
left join
lines cat
on
cat.id = art.line_id
left join
(select warehouses_details.article_id as prod,
sum(warehouses_details.quantity) as inicial 
from warehouses_details group by warehouses_details.article_id)war
on
war.prod=art.id

left join
(select purchases_details.article_id as prod, purchases_details.purchase_id as compra,
sum(purchases_details.quantity) as compras 
from purchases_details where purchases_details.purchase_id is not null group by purchases_details.article_id)pur
on
pur.prod=art.id

left join 
(select sales_details.article_id as prod,
sum(sales_details.quantity) as salidas 
from sales_details group by sales_details.article_id)sal
on
sal.prod=art.id
where cat.id=5 group by art.id;

here the output table: