VbzCart/docs/queries/qryStock byOpt andType: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==Details== * '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/table|cat_ioptns}}, {{vbzcart/table|cat_ittyps}} * '''Used by''': This is used for displaying the "stock" area on the w...) |
m (Woozle moved page VbzCart/VbzCart/queries/qryStock byOpt andType to VbzCart/docs/queries/qryStock byOpt andType without leaving a redirect: part 3/5) |
(No difference)
|
Latest revision as of 01:56, 25 February 2024
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 />