VbzCart/docs/queries/qryItTypsDepts grpItems: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(used by)
imported>Woozle
(using sub-query instead of doing the JOIN right here)
Line 1: Line 1:
==About==
==About==
* '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/table|cat_titles}}, {{vbzcart/query|v_stk_items_remaining}}
* '''Requires''': {{vbzcart/query|qryCat_Items_Stock}}, {{vbzcart/table|cat_titles}}
* '''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 v_stk_items_remaining; 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
==SQL==
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
Line 11: Line 11:
   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(IF(st.QtyForSale>0,1,0)) AS cntStkForSale, */
   /* SUM(IF(i.QtyForSale>0,1,0)) AS cntStkForSale, */
   SUM(st.qtyForSale) AS qtyForSale
   SUM(i.qtyForSale) AS qtyForSale
FROM (cat_items AS i LEFT JOIN v_stk_items_remaining AS st on i.ID=st.ID_Item) 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
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 />

Revision as of 01:29, 13 April 2009

About

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS SELECT 

 i.ID_ItTyp, t.ID_Dept,

 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 /* SUM(IF(i.QtyForSale>0,1,0)) AS cntStkForSale, */
 SUM(i.qtyForSale) AS qtyForSale

FROM qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID GROUP BY i.ID_ItTyp, t.ID_Dept HAVING cntForSale;</mysql> <section end=sql />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryItTypsDepts_grpItems&oldid=5256"