Page Comparison

SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection date: 'fieldCollectionNote_prefix,
+ d.remarks fieldCollectionNote
FROM
 ObjDates d
 JOIN Objects o on d.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND lower(d.eventType) IN ('date collected', 'secondary collection date', 'tertiary collection date')
 AND d.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection place: 'fieldCollectionNote_prefix,
+ x.remarks fieldCollectionNote
FROM
 SiteObjXrefs x
 JOIN Objects o ON x.objectID = o.objectID
WHERE
 o.IsVirtual = 0 AND o.IsTemplate = 0
 AND x.remarks IS NOT NULL
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collection place: 'fieldCollectionNote_prefix,
+ x.remarks fieldCollectionNote
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')
 AND x.remarks IS NOT NULL AND x.remarks != ''
UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 'Field collector: 'fieldCollectionNote_prefix,
+ x.remarks fieldCollectionNote
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')
 AND x.remarks IS NOT NULL
ORDER BY objectID
;

UNION ALL
SELECT
 CAST(o.objectID AS VARCHAR) objectID,
 (CASE WHEN t.textTypeID = 8 THEN 'NAGPRA site concordance note: ' WHEN t.textTypeID IN (14, 58, 102) THEN tt.textType + ': ' ELSE '' END) fieldCollectionNote_prefix,
 t.textEntry fieldCollectionNote
FROM
 TextEntries t
 JOIN Objects o ON t.ID = o.objectID
 JOIN TextTypes tt ON t.TextTypeID = tt.TextTypeID
WHERE
 t.tableID = 108
 AND o.IsVirtual = 0 AND o.IsTemplate = 0
 AND t.TextTypeID IN (8, 14, 58, 90, 102) 
;