VbzCart/queries/qryCat Items Stock: Difference between revisions
from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
New page: ==About== * '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/query|v_stk_items_remaining}} * '''Used by''': {{vbzcart/query|qryItTypsDepts grpItems}} * '''Notes''': This may turn out... |
underlying query renamed |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Requires''': {{vbzcart | * '''Requires''': {{vbzcart|table|cat_items}}, {{vbzcart/query|qryStk_items_remaining}} | ||
* '''Used by''': {{vbzcart/query| | * '''Used by''': {{vbzcart/query|qryItTypsDepts_grpItems}} | ||
* '''Notes''': This may turn out to be inefficient, since it retrieves a lot of data which may not be needed | * '''Notes''': This may turn out to be inefficient, since it retrieves a lot of data which may not be needed | ||
* '''History''': | * '''History''': | ||
** '''2009-04-12''' Created to replace cat_items.qtyInStock field | ** '''2009-04-12''' Created to replace cat_items.qtyInStock field | ||
** '''2009-11-29''' Underlying query renamed | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Items_Stock AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Items_Stock AS | ||
| Line 11: | Line 12: | ||
SUM(st.qtyForSale) AS qtyForSale, | SUM(st.qtyForSale) AS qtyForSale, | ||
SUM(st.qtyForShip) AS qtyForShip | SUM(st.qtyForShip) AS qtyForShip | ||
FROM (cat_items AS i LEFT JOIN | FROM (cat_items AS i LEFT JOIN qryStk_items_remaining AS st on i.ID=st.ID_Item) | ||
GROUP BY i.ID;</mysql> | GROUP BY i.ID;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 23:23, 29 November 2009
About
- Requires: cat_items, qryStk_items_remaining
- Used by: qryItTypsDepts_grpItems
- Notes: This may turn out to be inefficient, since it retrieves a lot of data which may not be needed
- History:
- 2009-04-12 Created to replace cat_items.qtyInStock field
- 2009-11-29 Underlying query renamed
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Items_Stock AS SELECT
i.*, SUM(st.qtyForSale) AS qtyForSale, SUM(st.qtyForShip) AS qtyForShip
FROM (cat_items AS i LEFT JOIN qryStk_items_remaining AS st on i.ID=st.ID_Item) GROUP BY i.ID;</mysql> <section end=sql />
