VbzCart/queries/qryCat Titles Item count
Jump to navigation
Jump to search
About
- Purpose: Subquery to count items for each title record
- Used by: a one-time maintenance query which might eventually become part of the catalog-building process. The objective was to be able to distinguish between (a) items which need a CatSfx added to the Title's CatKey because there are more than one of them per title. and (b) items whose catalog numbers can be generated without a CatSfx. If an item falls into the former category but its CatSfx is NULL, this represents a maintenance issue -- probably leftover data from before that field was created, but might also indicate a bug in the catalog building.
- Note: This is very similar to
- REDIRECT Template:l/vc/query, but does not bring in stock information -- therefore faster and possibly including more records.
- History:
- 2010-11-11 Created.
SQL
<mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS SELECT
ID_Title, ID_Dept, DateAdded AS WhenAdded, COUNT(i.ID) AS cntItems, SUM(IF(i.isForSale,1,0)) AS cntForSale, SUM(IF(i.isInPrint,1,0)) AS cntInPrint, MIN(i.PriceSell) AS currMinSell, MAX(i.PriceSell) AS currMaxSell, t.Name
FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID GROUP BY i.ID_Title;</mysql>
maintenance query
This is the temporary query for which this query was created: <mysql>SELECT
i.ID, i.ID_Title, i.CatNum, i.CatSfx, t.cntItems
FROM cat_items AS i
LEFT JOIN qryCat_Titles_Item_count AS t ON t.ID_Title=i.ID_Title WHERE (i.CatSfx IS NULL) AND (NOT i.isPulled) AND (t.cntItems>1) AND (CatNum IS NOT NULL) ORDER BY CatNum;</mysql>
That version won't let you edit the records; this one will, although it includes a lot of records which don't need to be edited -- use the above query to find them. <mysql>SELECT
ID, ID_Title, CatNum, CatSfx
FROM cat_items WHERE (CatSfx IS NULL) AND (NOT isPulled) AND (CatNum IS NOT NULL) ORDER BY CatNum;</mysql>