Difference between revisions of "VbzCart/coding/integrity checks"
Jump to navigation
Jump to search
(Created page with "Table-specific checks: * {{l/vc/table|cat_items/integrity checks}} ==Cross-table checks== Package lines and order lines which don't agree about what ID_Item they're tracking:...") |
m (fixed markup tags) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 2: | Line 2: | ||
* {{l/vc/table|cat_items/integrity checks}} | * {{l/vc/table|cat_items/integrity checks}} | ||
==Cross-table checks== | ==Cross-table checks== | ||
| − | Package | + | Package Lines and Order Lines which don't agree about what ID_Item they're tracking: |
| − | <mysql>SELECT | + | <source lang=mysql>SELECT |
* | * | ||
FROM | FROM | ||
| Line 10: | Line 10: | ||
ord_lines AS ol ON pl.ID_OrdLine = ol.ID | ord_lines AS ol ON pl.ID_OrdLine = ol.ID | ||
WHERE | WHERE | ||
| − | ol.ID_Item != pl.ID_Item</mysql> | + | ol.ID_Item != pl.ID_Item</source> |
| + | Package lines whose Order Line is either not set or nonexistent(!): | ||
| + | <source lang=mysql>SELECT | ||
| + | pl.* | ||
| + | FROM | ||
| + | ord_pkg_lines AS pl | ||
| + | LEFT JOIN | ||
| + | ord_lines AS ol ON pl.ID_OrdLine = ol.ID | ||
| + | WHERE | ||
| + | ol.ID IS NULL</source> | ||
Latest revision as of 22:04, 16 July 2019
Table-specific checks:
Cross-table checks
Package Lines and Order Lines which don't agree about what ID_Item they're tracking:
SELECT
*
FROM
ord_pkg_lines AS pl
JOIN
ord_lines AS ol ON pl.ID_OrdLine = ol.ID
WHERE
ol.ID_Item != pl.ID_Item
Package lines whose Order Line is either not set or nonexistent(!):
SELECT
pl.*
FROM
ord_pkg_lines AS pl
LEFT JOIN
ord_lines AS ol ON pl.ID_OrdLine = ol.ID
WHERE
ol.ID IS NULL