CODEWITHSUNDEEP
mysqlsql
André Silva
André Silva

Reputation: 81

MySQL COUNT items in stock

Currently I have these views:

CREATE View InMaterial AS 
 Select Cat.descricao as Category, 
        Mar.descricao as Brand, 
        Mo.descricao as Model, 
        COUNT(Ent.n_serie) as Qty 
   From Categoria Cat, Marca Mar, Modelo Mo, Material Mat, Entrada Ent 
  Where Cat.id_categoria=Mo.categoria 
    AND Mar.id_marca = Mo.id_marca 
    AND Mo.id_modelo = Mat.id_modelo 
    AND Mat.n_serie = Ent.n_serie 
Group by Cat.descricao,Mar.descricao,Mo.descricao;

Create View OutMaterial As 
 Select Cat.descricao as Category, 
        Mar.descricao as Brand, 
        Mo.descricao as Model, 
        COUNT(Sai.n_serie) as Qty 
   From Categoria Cat, Marca Mar, Modelo Mo, Material Mat, Saida Sai 
  Where Cat.id_categoria=Mo.categoria 
    AND Mar.id_marca = Mo.id_marca 
    AND Mo.id_modelo = Mat.id_modelo 
    AND Mat.n_serie = Sai.n_serie 
 Group by Cat.descricao,Mar.descricao,Mo.descricao;

Create View Stocks AS 
 Select E.Categoria as Category, 
        E.Marca as Brand, 
        E.Modelo as Model, 
        E.Qty - S.Qty as Quantity 
   From InMaterial E, OutMaterial S 
  Where E.Brand=S.Brand 
    AND E.Model=S.Model 
    AND E.Category=S.Category 
 GROUP BY Category,Brand,Model;

The results of these views is shown below:

InMaterial

Category Brand     Model      Qty
 FF      JUNIPER   556655     2
 SFP     CISCO     2901       2

OutMaterial

Category Brand     Model      Qty
 FF      JUNIPER   556655     1

Stock

Category Brand     Model      Qty
 FF      JUNIPER   556655     1

As you can see, I've sort of "dispatched" 1 FF Juniper 556655 out of the warehouse, so I should keep 2 CISCO 500 and 1 Juniper 556655.

The purpose of these views is to shown the amount (qty) of each item, though when I run a select on stock's view, the 2 SFP CISCO 500 do not show, but they are in stock.

I'm looking for a solution for this problem as I have to finish off this project really soon...

Upvotes: 2

Views: 782

Answers (1)

mjsqu
mjsqu

Reputation: 5452

You should redefine the Stocks view as a LEFT JOIN. Left joins take all the columns from the first table regardless of matches in the second.

This is the kind of behaviour that is desired in this case, because you cannot have OutMaterial rows, that were not previously in InMaterial (I'm assuming).

New view definition:

Create View Stocks AS 
Select E.Categoria as Category, 
E.Marca as Brand, 
E.Modelo as Model, 
E.Qty - (coalesce(S.Qty,0)) as Quantity 
From InMaterial E
LEFT JOIN
OutMaterial S 
ON 
E.Brand=S.Brand AND 
E.Model=S.Model AND 
E.Category=S.Category 
GROUP BY Category,Brand,Model;

You'll notice that I've also redefined Quantity, the coalesce part changes a missing value to zero.

Upvotes: 1

Related Questions