...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT
obj.objectID objectID,
acc.objectID acquisitionID
FROM
Objects obj,
Associations oa,
Objects acc
WHERE
obj.IsVirtual = 0 AND obj.IsTemplate = 0
AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
AND obj.objectID = oa.ID2
AND oa.ID1 = acc.objectID
;
|
notes
...
UNION ALL
SELECT
obj.objectID objectID,
acc.objectID acquisitionID
FROM
Objects obj,
ObjAccession oa,
Objects acc
WHERE
obj.IsVirtual = 0 AND obj.IsTemplate = 0
AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
AND obj.objectID = oa.objectID
AND (CASE WHEN oa.acquisitionNumber NOT LIKE 'ACC.%' THEN 'ACC.' END) + REPLACE(oa.acquisitionNumber, ' ', '') = acc.objectNumber
AND obj.objectID NOT IN
(SELECT
obj.objectID
FROM
Objects obj,
Associations oa,
Objects acc
WHERE
obj.IsVirtual = 0 AND obj.IsTemplate = 0
AND acc.IsVirtual = 1 AND acc.IsTemplate = 0 AND acc.objectNumber LIKE 'ACC%'
AND obj.objectID = oa.ID2
AND oa.ID1 = acc.objectID
)
;
|
notes
- second half of the query includes objects that are not linked to an accession record via the Associations table, but do have an accession number in the ObjAccession table which references (after removing spaces and prepending 'ACC.') a valid accession record