Difference between revisions of "VbzCart/queries/qryCtg src sub"
Jump to navigation
Jump to search
m (2008-03-15) |
(eliminate duplicate rows) |
||
Line 3: | Line 3: | ||
** '''Queries''': {{vbzcart/query|qryCtg_Titles_active}}, {{vbzcart/query|qryCtg_Sources_active}}, {{vbzcart/query|qryCtg_Items_active}} | ** '''Queries''': {{vbzcart/query|qryCtg_Titles_active}}, {{vbzcart/query|qryCtg_Sources_active}}, {{vbzcart/query|qryCtg_Items_active}} | ||
** '''Tables''': {{vbzcart|table|ctg_groups}}, {{vbzcart|table|cat_ioptns}}, {{vbzcart|table|cat_ittyps}} | ** '''Tables''': {{vbzcart|table|ctg_groups}}, {{vbzcart|table|cat_ioptns}}, {{vbzcart|table|cat_ittyps}} | ||
− | * '''Used by''': [[VbzCart catalog building|catalog building process]] | + | * '''Used by''': [[VbzCart/catalog/building|catalog building process]], which saves output in {{vbzcart|table|ctg_upd1}} |
* '''History''': | * '''History''': | ||
** '''2008-03-15''' Building sequence completely rewritten, so presumably this query was created then | ** '''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) | ** '''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 | ||
==SQL== | ==SQL== | ||
− | + | <mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS | |
− | SELECT | + | SELECT DISTINCT |
NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx, | NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx, | ||
c.isCloseOut, | c.isCloseOut, | ||
− | |||
gi.ID AS ID_CTG_Item, | gi.ID AS ID_CTG_Item, | ||
− | |||
gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */ | gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */ | ||
gt.ID_Title, | gt.ID_Title, | ||
Line 43: | Line 42: | ||
) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.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> | WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql> | ||
− |
Revision as of 01:03, 18 August 2014
About
- Requires:
- Queries:
- 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
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>