VbzCart/queries/qryTitleTopic Title avail

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to: navigation, search

About

  • Returns: list of topics with title availability stats
  • Used by: anywhere a list of topics is displayed
  • Requires: cat_title_x_topic, _titles
  • History:
    • 2011-02-25 created for Topic listings everywhere
    • 2011-09-28 Topic tables renamed

SQL

<mysql>CREATE OR REPLACE VIEW qryTitleTopic_Title_avail AS SELECT

 tt.ID_Topic,
 SUM(ti.cntForSale) AS cntForSale,
 SUM(ti.cntInPrint) AS cntInPrint,
 SUM(ti.qtyInStock) AS qtyInStock

FROM cat_title_x_topic AS tt LEFT JOIN _titles AS ti ON tt.ID_Title=ti.ID WHERE ti.cntForSale>0 GROUP BY tt.ID_Topic;</mysql>