VbzCart/queries/qryCat Titles Item count

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

1. 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>