match_isbn_to_volume.sql

SELECT
r.record_num as bib_num,
s.content as marc_020,
v.field_content as vol

FROM
sierra_view.record_metadata 	as r

-- if we want to get best_title, we can select p.best_title and uncomment below
-- JOIN
-- sierra_view.bib_record_property	as p
-- ON
--   p.bib_record_id = r.id

-- get the volume info... if any
LEFT OUTER JOIN
sierra_view.varfield	as v
ON
  (v.record_id = r.id) AND (v.varfield_type_code = 'v')

LEFT OUTER JOIN
sierra_view.bib_record_item_record_link	as l
ON
  l.bib_record_id = r.id

LEFT OUTER JOIN
sierra_view.item_record	as i
ON
  i.record_id = l.item_record_id

LEFT OUTER JOIN
sierra_view.subfield		as s
ON
  (s.record_id = r.id) AND (s.marc_tag = '020')

WHERE
i.itype_code_num = 2

ORDER BY
bib_num,
marc_020,
vol