Difference between revisions of "VbzCart/queries/qryStock byOpt andType"
Jump to navigation
Jump to search
(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...) |
(No difference)
|
Latest revision as of 02:36, 4 December 2008
Details
- Requires:
- 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>