VbzCart/docs/queries/qryStock by Supp Type Opt: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==About== * '''Returns''': Stock totals by supplier+itemtype+itemoption * '''Requires''': * '''Used by''': stock-by-size index page (static version at least; dynamic version may need cache...) |
(No difference)
|
Revision as of 02:29, 10 December 2008
About
- Returns: Stock totals by supplier+itemtype+itemoption
- Requires:
- Used by: stock-by-size index page (static version at least; dynamic version may need cached tables, but we'll see)
- History:
- 2008-12-09 Rewritten almost from scratch (loosely based on existing Access query), not sure how the old one worked
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_by_Supp_Type_Opt AS SELECT
t.ID_Supplier, io.CatKey, it.NameSng AS TypeSing, IFNULL(it.NamePlr,it.NameSng) AS TypePlur, SUM(stk.QtyForSale) AS QtyForSale, Count(it.ID) AS TypeCount
FROM
(((cat_ioptns AS io LEFT JOIN cat_items AS i ON i.ID_ItOpt=io.ID) LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID) LEFT JOIN qryStkItms_for_sale AS stk ON i.ID=stk.ID_Item
GROUP BY t.ID_Supplier, it.NamePlr, it.NameSng, io.CatKey HAVING QtyForSale;</mysql> <section end=sql />