VbzCart/queries/qryTitles Item info

About

• Purpose: Lists titles with some additional item-oriented info
• Requires: cat_items,

1. REDIRECT Template:l/vc/query

• 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

<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>