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>