*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
https://github.com/cul-it/cul-folio-analytics/tree/main/metadb/canned_reports/MCR413
Here is an example of a query that shows two srs_id records that correspond to one instance record on the folio_source_record.marc _ _ t table along with the result. Note the join from the inventory table to the marc _ _ table. It isolates just one instance_hrid in the WHERE clause. The result shows one instance_hrid record associated with two srs_id records. One srs_id record shows a state of 'ACTUAL,' and the other shows a state of 'OLD'. This results in inflated record counts.
Sample 2:
SELECT
...
DISTINCT
instance_ext.instance_hrid,
marc__t.instance_id,
marc__t.srs_id,
records_lb.state
FROM
...
folio_derived.instance_
...
ext
LEFT
...
JOIN
...
folio_source_record.marc__t
ON
...
instance_ext.instance_id
...
=
...
marc__t.instance_id
LEFT
...
JOIN
...
folio_source_record.records_
...
lb
ON
...
marc__t.instance_id
...
=
...
records_lb.external_id
AND
...
marc__t.srs_id
...
=
...
WHERE
...
instance_ext.instance_hrid
...
=
...
'11023049'
...
Result:
And if we wanted to show the WHERE condition “records_lb.state = ‘ACTUAL’ “ we could add it onto the same query as follows. Using 'ACTUAL' in the WHERE clause results in one srs_id associated with one instance_hrid. The count of records is no longer inflated.
Sample 3:
SELECT
...
DISTINCT
instance_ext.instance_hrid,
marc__t.instance_id,
marc__t.srs_id,
records_lb.state
FROM
...
folio_derived.instance_
...
ext
LEFT
...
JOIN
...
folio_source_record.marc__t
ON
...
instance_ext.instance_id
...
=
...
marc__t.instance_id
LEFT
...
JOIN
...
folio_source_record.records_
...
lb
ON
...
marc__t.instance_id
...
=
...
records_lb.external_id
AND
...
marc__t.srs_id
...
=
...
WHERE
...
instance_ext.instance_hrid
...
=
...
'11023049'
...
and records_lb.state
...
=
...
'ACTUAL'
...
Result:
...
