VbzCart/queries/qryTitles Item info: Difference between revisions
from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
some more fields |
renamed count field for consistency |
||
| Line 9: | Line 9: | ||
SELECT | SELECT | ||
t.*, | t.*, | ||
COUNT(i.ID) AS | COUNT(i.ID) AS cntItems, | ||
SUM(s.QtyForSale) AS qtyForSale, | SUM(s.QtyForSale) AS qtyForSale, | ||
SUM(i.isForSale) AS cntForSale, | SUM(i.isForSale) AS cntForSale, | ||
Latest revision as of 19:55, 7 November 2010
About
- Purpose: Lists titles with some additional item-oriented info
- Requires: cat_items, qryStk_items_remaining
- Used by: titles AdminList() (replaces qryCat_Titles_Item_stats)
- History:
- 2010-11-07 Created for titles AdminList(): qryCat_Titles_Item_stats was not showing titles that didn't have any Item records
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_Item_info AS SELECT
t.*, COUNT(i.ID) AS cntItems, SUM(s.QtyForSale) AS qtyForSale, SUM(i.isForSale) AS cntForSale, SUM(i.isInPrint) AS cntInPrint, MIN(i.PriceSell) AS dlrMinSell, MAX(i.PriceSell) AS dlrMaxSell
FROM (cat_titles AS t
LEFT JOIN cat_items AS i ON i.ID_Title=t.ID) LEFT JOIN qryStk_items_remaining AS s ON i.ID=s.ID_Item
GROUP BY t.ID; </mysql> <section end=sql />
