VbzCart/queries/qryTitles ItTyps grpItems

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | queries
Revision as of 18:34, 30 November 2008 by Woozle (talk | contribs) (moved from single-page listing)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Details

This is a subquery for qryTitles_ItTyps; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.

  • Notes:
    • I originally had a HAVING cntInStock at the end of this, but that results in only stock items showing as available at all. If this filtering is needed for something, it will have to be expressed in some other way.
  • History:
    • 2008-04-04 Grouping by GrpCode (and GrpDescr and GrpSort) is not necessary, and creates clutter in the title display if used. Certain items which have subtypes only distinguished by the group code (e.g. SD-NF-320 comes in both black and tie-dye) use these fields, but the page-generation code looks for different subtypes in the cat_items data and displays a new subtype header whenever GrpDescr changes. Maybe later on we'll include ID_CtgGroup in cat_items and pull GrpCode, GrpDescr, and GrpSort from the appropriate table(s?), but for now we store the values explicitly in cat_items and rely on proper sorting.
      • If subtypes are not being properly disambiguated within the title display, make sure the GrpDescr fields are being given a value (description). This is only necessary when the item type (ID_ItTyp) is the same.

SQL

<mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS SELECT

 ID_Title, ID_ItTyp,
 SUM(IF(isForSale,1,0)) AS cntForSale,
 SUM(IF(isInPrint,1,0)) AS cntInPrint,
 SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
 SUM(qtyInStock) AS qtyInStock,
 MIN(PriceSell) AS currMinPrice,
 MAX(PriceSell) AS currMaxPrice

FROM cat_items AS i WHERE isForSale GROUP BY ID_Title, ID_ItTyp;</mysql>