VbzCart/docs/queries/qryStock byOpt andType

Details

• Requires: Template:Vbzcart/table, Template:Vbzcart/table, Template:Vbzcart/table
• Used by: This is used for displaying the "stock" area on the web. The basic records are items, but they're only ones that are in stock, so "Stock" is shorthand for "items that are in stock" in this case.

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS SELECT

 i.ID_ItTyp,
 i.ID_ItOpt,
 SUM(i.qtyInStock) AS qtyInStock,
 it.NameSng AS It_NameSng,
 it.NamePlr AS It_NamePlr,
 io.CatKey AS Io_CatKey,
 io.Descr AS Io_Descr,
 IFNULL(it.Sort,'ZZZ') AS It_Sort

FROM (cat_items AS i LEFT JOIN cat_ioptns AS io ON i.ID_ItOpt=io.ID)

 LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID

GROUP BY i.ID_ItTyp, i.ID_ItOpt, io.Sort HAVING SUM(i.qtyInStock) ORDER BY It_Sort,io.Sort;</mysql> <section end=sql />