Difference between revisions of "VbzCart/queries/qryCat Titles Item count"
Jump to navigation
Jump to search
(Created page with '==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-build…') |
(refinements to maintenance query) |
||
(One intermediate revision by the same user not shown) | |||
Line 4: | Line 4: | ||
* '''Note''': This is very similar to {{vbzcart/query|qryCat_Titles_Item_stats}}, but does not bring in stock information -- therefore faster and possibly including more records. | * '''Note''': This is very similar to {{vbzcart/query|qryCat_Titles_Item_stats}}, but does not bring in stock information -- therefore faster and possibly including more records. | ||
* '''History''': | * '''History''': | ||
− | ** '''2010-11-11 Created. | + | ** '''2010-11-11''' Created. |
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS | ||
Line 31: | Line 31: | ||
LEFT JOIN qryCat_Titles_Item_count AS t | LEFT JOIN qryCat_Titles_Item_count AS t | ||
ON t.ID_Title=i.ID_Title | ON t.ID_Title=i.ID_Title | ||
− | WHERE (CatSfx IS NULL) AND (NOT isPulled) AND (cntItems>1);</mysql> | + | 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> |
Latest revision as of 22:58, 11 November 2010
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>