VbzCart/queries/qryStock byOpt andType

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search

Details

  • Requires:
  1. REDIRECT Template:l/vc/table, cat_ioptns, cat_ittyps
  • 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

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

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryStock_byOpt_andType&oldid=10955"