Difference between revisions of "VbzCart/queries"
< VbzCart
Jump to navigation
Jump to search
(→Views: qryCat_Depts; need depts) |
(→Views: added creation code) |
||
| Line 8: | Line 8: | ||
* '''qryCat_''': queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was '''qryCatNum_'''.) | * '''qryCat_''': queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was '''qryCatNum_'''.) | ||
===qryCat_Depts=== | ===qryCat_Depts=== | ||
| − | < | + | <mysql> |
| − | + | CREATE OR REPLACE VIEW qryCat_Depts AS | |
| − | + | SELECT | |
| − | + | d.ID, | |
| − | + | d.Name, | |
| − | + | d.Sort, | |
| − | + | d.CatKey, | |
| − | FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</ | + | d.ID_Supplier, |
| + | CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum | ||
| + | FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql> | ||
===qryCat_Titles=== | ===qryCat_Titles=== | ||
| − | <sql>SELECT | + | <sql>CREATE OR REPLACE VIEW qryCat_Titles AS |
| − | + | SELECT | |
| − | + | t.ID, | |
| − | + | t.Name, | |
| − | + | CONCAT_WS('-',d.CatNum,t.CatKey) AS CatNum, | |
| − | + | d.ID_Supplier, | |
| − | + | t.ID_Dept, | |
| − | + | t.DateAdded, | |
| − | + | t.RstkMin AS QtyMin_Rstk, | |
| − | + | t.Notes, | |
| − | + | t.Supplier_CatNum AS Supp_CatNum | |
| − | FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</sql> | + | FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</sql> |
===qryCat_Items=== | ===qryCat_Items=== | ||
<sql>SELECT | <sql>SELECT | ||
Revision as of 18:10, 3 October 2007
Overview
"Views" in MySQL are like SELECT "queries" in MS Access, i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).
Views
Some common prefixes:
- qryCbx_: queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further qryCbx_ queries which show a subset of the results.
- qryCat_: queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was qryCatNum_.)
qryCat_Depts
<mysql> CREATE OR REPLACE VIEW qryCat_Depts AS
SELECT
d.ID,
d.Name,
d.Sort,
d.CatKey,
d.ID_Supplier,
CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum
FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql>
qryCat_Titles
<sql>CREATE OR REPLACE VIEW qryCat_Titles AS
SELECT
t.ID,
t.Name,
CONCAT_WS('-',d.CatNum,t.CatKey) AS CatNum,
d.ID_Supplier,
t.ID_Dept,
t.DateAdded,
t.RstkMin AS QtyMin_Rstk,
t.Notes,
t.Supplier_CatNum AS Supp_CatNum
FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</sql>
qryCat_Items
<sql>SELECT
i.ID,
i.ID_Title,
(t.ID IS NOT NULL) AS TitleExists,
t.ID_Supplier,
i.PriceSell,
i.PriceList,
i.PriceBuy,
i.CatNum,
t.CatNum AS Title_CatNum,
t.Name AS Title_Name,
i.ID_ItTyp,
i.ID_ItOpt,
i.ItOpt_Descr,
i.ItOpt_Sort,
CONCAT(t.Name, IF(i.ItOpt_Descr IS NULL,,CONCAT(' (',i.ItOpt_Descr,')')) AS Descr,
i.Supp_CatNum,
i.isForSale,
i.isPulled,
i.QtyMin_Stk,
t.QtyMin_Rstk AS QtyMin_Rstk_Title
FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</sql>
qryCbx_Items
<sql>SELECT
i.ID,
CONCAT(
IF(i.isPulled,CONCAT('!PULLED (',i.ID,')'),),
i.CatNum,
IF(i.Supp_CatNum IS NULL,,CONCAT('[',i.SuppCatNum,']')),
IF(i.isForSale,,' n/a'),
i.Descr)
AS Descr,
i.isForSale
FROM qryCat_Items AS i ORDER BY NOT i.isPulled, i.isForSale, i.CatNum;</sql>