Page Comparison

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 o.MTB_PlaceTermID fieldCollectionPlace,
 o.provenance fieldLocVerbatim
FROM
 Objects o
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND (o.MTB_PlaceTermID IS NOT NULL OR o.provenance IS NOT NULL)
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 s.MTB_PlaceTermID1 fieldCollectionPlace,
 COALESCE(x.subsite1, '') + (CASE WHEN x.subsite1 IS NOT NULL AND x.subsite2 IS NOT NULL THEN ', ' ELSE '' END) + COALESCE(x.subsite2, '') + (CASE WHEN x.subsite3 IS NOT NULL THEN ', ' + x.subsite3 ELSE '' END) fieldLocVerbatim
FROM
 SiteObjXrefs x
 JOIN Objects o ON x.objectID = o.objectID
 JOIN Sites s ON x.siteID = s.siteID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND s.MTB_PlaceTermID1 IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 g.MTB_PlaceTermID fieldCollectionPlace,
 NULL fieldLocVerbatim
FROM
 ObjGeography g
 JOIN Objects o ON g.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND g.MTB_PlaceTermID IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.termID fieldCollectionPlace,
 NULL fieldLocVerbatim
FROM
 ThesXrefs x
 JOIN ThesXrefTypes t ON x.thesXrefTypeID = t.thesXrefTypeID
 JOIN Objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.thesXrefTableID = 346
 AND lower(t.thesXrefType) IN ('archaeological site', 'card header', 'provenience')
;

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 x.constituentID fieldCollector
FROM
 ConXrefs x
 JOIN roles r ON x.roleID = r.roleID
 JOIN objects o ON x.ID = o.objectID
WHERE
 x.tableID = 108
 AND x.active = 1
 AND  o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(r.ROLE) IN ('collector', 'secondary collector', 'tertiary collector')
;