Page History

...

Sample 1:

FROM folio_derived.instance_ext AS instext
LEFT JOIN folio_source_record.marc__t AS sm ON instext.instance_id = sm.instance_id
LEFT JOIN folio_source_record.records_lb AS rec ON sm.instance_id = rec.external_id
WHERE recWHERE rec.state = 'ACTUAL' 

Here is an examples of a query that uses 'ACTUAL' :

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 = records_lb.id

WHERE 

...

instance_ext.instance_hrid = '11023049'
;

Result:

Image Added

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 = records_lb.id
WHERE instance_ext.instance_hrid = '11023049'
AND records_lb.state = 'ACTUAL'
;
 

Result:

Image Added