For reference, running this query in a copy of our production data:
SELECT
bmp.id AS part_id, bmp.record AS part_record, acn.record AS cn_record
FROM
asset.copy ac
JOIN asset.call_number acn ON ac.call_number = acn.id
JOIN asset.copy_part_map acpm ON ac.id = acpm.target_copy
JOIN biblio.monograph_part bmp ON acpm.part = bmp.id
WHERE
NOT ac.deleted
AND acn.record <> bmp.record
Gives me 37 rows. 34 if I add a distinct on bmp.id, 19 if I distinct on the combination of the two record IDs.
For reference, running this query in a copy of our production data:
SELECT monograph_ part bmp ON acpm.part = bmp.id
bmp.id AS part_id, bmp.record AS part_record, acn.record AS cn_record
FROM
asset.copy ac
JOIN asset.call_number acn ON ac.call_number = acn.id
JOIN asset.copy_part_map acpm ON ac.id = acpm.target_copy
JOIN biblio.
WHERE
NOT ac.deleted
AND acn.record <> bmp.record
Gives me 37 rows. 34 if I add a distinct on bmp.id, 19 if I distinct on the combination of the two record IDs.