VbzCart/procs/Upd SupplierIttyps

From HTYP, the free directory anyone can edit

[edit] About

• Input: cat_supp, _titles, _title_ittyps, cat_ittyps
• Output: _supplier_ittyps (delete/insert)
• Notes: Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).

[edit] SQL

  CREATE PROCEDURE Upd_SupplierIttyps()
    BEGIN
      DELETE FROM _supplier_ittyps;
      INSERT INTO _supplier_ittyps
      SELECT
        s.ID,
        if(Count(ti.ID_Title)=1,it.NameSng,it.NamePlr) AS ItemType,
        Count(ti.ID_Title) AS ItemCount,
        s.Name, s.CatKey
      FROM (
        (cat_supp AS s LEFT JOIN _titles AS tc ON tc.ID_Supp=s.ID)
        LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
        LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
      GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
      HAVING SUM(tc.cntForSale)
      ORDER BY s.Name, SUM(tc.cntForSale) DESC;
    END