VbzCart/docs/procs/Upd SupplierIttyps: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | procs
Jump to navigation Jump to search
imported>Woozle
(New page: ==About== * '''Input''': {{vbzcart|table|cat_supp}}, {{vbzcart|table|_titles}}, {{vbzcart|table|_title_ittyps}}, {{vbzcart|table|cat_ittyps}} * '''Output''': {{vbzcart|table|_supplier_itty...)
(No difference)

Revision as of 20:40, 8 March 2009

About

SQL

<section begin=sql /><mysql> 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</mysql>

<section end=sql />