VbzCart/docs/queries/qryItTypsDepts grpItems: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (using sub-query instead of doing the JOIN right here) |
m (Woozle moved page VbzCart/VbzCart/queries/qryItTypsDepts grpItems to VbzCart/docs/queries/qryItTypsDepts grpItems without leaving a redirect: part 2) |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}} | * '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}, {{vbzcart/table|cat_depts}} | ||
* '''Used by''': {{vbzcart/query|qryItTypsDepts_ItTyps}} | * '''Used by''': {{vbzcart/query|qryItTypsDepts_ItTyps}} | ||
* '''History''': | * '''History''': | ||
** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for | ** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for | ||
** '''2009-11-29''' Adding ID_Supplier for more efficient coding (see {{vbzcart/query|qryItTypsDepts_ItTyps}}) | |||
*** Also removing commented-out cntStkForSale | |||
* '''Notes''': | |||
** This is essentially the same query as {{vbzcart/query|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp). | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS | ||
SELECT | SELECT | ||
i.ID_ItTyp, t.ID_Dept, | i.ID_ItTyp, t.ID_Dept, d.ID_Supplier, | ||
SUM(IF(i.isForSale,1,0)) AS cntForSale, | SUM(IF(i.isForSale,1,0)) AS cntForSale, | ||
SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | SUM(IF(i.isInPrint,1,0)) AS cntInPrint, | ||
SUM(i.qtyForSale) AS qtyForSale | SUM(i.qtyForSale) AS qtyForSale | ||
FROM qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID | FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID | ||
GROUP BY i.ID_ItTyp, t.ID_Dept | GROUP BY i.ID_ItTyp, t.ID_Dept | ||
HAVING cntForSale;</mysql> | HAVING cntForSale;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 01:55, 25 February 2024
About
- Requires: Template:Vbzcart/query, Template:Vbzcart/table, Template:Vbzcart/table
- Used by: Template:Vbzcart/query
- History:
- 2009-04-12 No longer caching stock-quantity fields in cat_items, so had to pull in
v_stk_items_remainingqryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for - 2009-11-29 Adding ID_Supplier for more efficient coding (see Template:Vbzcart/query)
- Also removing commented-out cntStkForSale
- 2009-04-12 No longer caching stock-quantity fields in cat_items, so had to pull in
- Notes:
- This is essentially the same query as Template:Vbzcart/query but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS SELECT
i.ID_ItTyp, t.ID_Dept, d.ID_Supplier, SUM(IF(i.isForSale,1,0)) AS cntForSale, SUM(IF(i.isInPrint,1,0)) AS cntInPrint, SUM(i.qtyForSale) AS qtyForSale
FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID GROUP BY i.ID_ItTyp, t.ID_Dept HAVING cntForSale;</mysql> <section end=sql />