VbzCart/docs/coding/integrity checks: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (another ol-pl cross-check) |
m (Woozle moved page VbzCart/VbzCart/coding/integrity checks to VbzCart/docs/coding/integrity checks without leaving a redirect: correct naming (was no way to import directly to this name)) |
||
| (One intermediate revision by one other user not shown) | |||
| Line 3: | Line 3: | ||
==Cross-table checks== | ==Cross-table checks== | ||
Package Lines and Order Lines which don't agree about what ID_Item they're tracking: | 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</ | ol.ID_Item != pl.ID_Item</source> | ||
Package lines whose Order Line is either not set or nonexistent(!): | Package lines whose Order Line is either not set or nonexistent(!): | ||
<mysql>SELECT | <source lang=mysql>SELECT | ||
pl.* | pl.* | ||
FROM | FROM | ||
| Line 19: | Line 19: | ||
ord_lines AS ol ON pl.ID_OrdLine = ol.ID | ord_lines AS ol ON pl.ID_OrdLine = ol.ID | ||
WHERE | WHERE | ||
ol.ID IS NULL</ | ol.ID IS NULL</source> | ||
Latest revision as of 01:53, 25 February 2024
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