VbzCart/queries/qryCtg src sub

About

• Requires:
  • Queries:

1. REDIRECT Template:l/vc/query, qryCtg_Sources_active, qryCtg_Items_active

• • Tables: ctg_groups, cat_ioptns, cat_ittyps
• Used by: catalog building process, which saves output in ctg_upd1
• History:
  • 2008-03-15 Building sequence completely rewritten, so presumably this query was created then
  • 2009-05-03 (dev note) Moved doc to separate page; added query to main server (no design change)
  • 2014-08-17 Added DISTINCTROW and removed gt.ID AS ID_CTG_Title and gt.ID_Group AS ID_CTG_Group to eliminate duplicate entries in ctg_upd1 when same title belongs to multiple groups
  • 2016-03-02 This query no longer works, and shouldn't be needed anyway.

SQL

<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS SELECT DISTINCT

   NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),) AS CatSfx,
   c.isCloseOut,
   gi.ID AS ID_CTG_Item,
   gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */
   gt.ID_Title,
   gi.ID_ItTyp,
   gi.ID_ItOpt,
   gi.ID_ShipCost,
   gi.PriceBuy,
   gi.PriceSell,
   gi.PriceList,
   IFNULL(gi.Descr,CONCAT_WS(' / ',gt.GroupDescr,o.Descr)) AS ItOpt_Descr_part,
   it.NameSng,
   gi.Descr AS GrpItmDescr,
   gt.GroupDescr AS TitleGroupDescr,
   o.Descr AS OptionDescr,
   CONCAT(g.Sort,it.Sort,o.Sort) AS ItOpt_Sort,
   gt.GroupCode AS GrpCode,
   gt.GroupDescr AS GrpDescr,
   gt.GroupSort AS GrpSort
 FROM
 (
   (
     (
       (
         qryCtg_Titles_active AS gt LEFT JOIN ctg_groups AS g ON g.ID=gt.ID_Group
        ) LEFT JOIN qryCtg_Sources_active AS c ON gt.ID_Source=c.ID
      ) LEFT JOIN qryCtg_Items_active AS gi ON gi.ID_Group=g.ID
    ) LEFT JOIN cat_ioptns AS o ON gi.ID_ItOpt=o.ID
  ) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID

WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>