SQL query for getting all the "Course Reserves" for library location SEL, from Sierra ILMS. Produces CourseNumber, Book Title, and LoC Call Number. Sample Output: "AAE 2200 (18L)";"Introduction to flight";"|aTL570|b.A68 2012" "AAE 3520 (18L)";"Modern flight dynamics";"|aTL570|b.S298 2012" "AAE 3542 (18L)";"Aircraft structures for engineering stu…

sel-course-reserves.sql

SELECT 
  distinct on (varfield_view.field_content, bib_view.title, varfield.field_content) 
  varfield_view.field_content as course_number, 
  bib_view.title, 
  varfield.field_content as call_number
FROM 
  sierra_view.course_record, 
  sierra_view.course_record_item_record_link, 
  sierra_view.bib_view, 
  sierra_view.bib_record_item_record_link, 
  sierra_view.varfield_view, 
  sierra_view.varfield varfield
WHERE 
  course_record_item_record_link.course_record_id = course_record.record_id AND
  bib_record_item_record_link.item_record_id = course_record_item_record_link.item_record_id AND
  bib_record_item_record_link.bib_record_id = bib_view.id AND
  varfield_view.record_id = course_record.record_id AND
  varfield.record_id = bib_record_item_record_link.bib_record_id AND
  course_record.location_code = 'sel' AND 
  varfield_view.record_type_code = 'r' AND 
  varfield_view.varfield_type_code = 'r' AND 
  (varfield.marc_tag = '090' OR 
  varfield.marc_tag = '050')
ORDER BY
  varfield_view.field_content ASC, 
  bib_view.title ASC;