collection_detail_view (view)

4,989,776 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: bib_cat_date (date), creation_date (date), record_last_update (date), item_creation_date (date), item_last_update (date)

Next page

CREATE VIEW collection_detail_view AS with collection_detail_data as (
  select
    -- br.record_id,
    rm.record_num as bib_record_num,
    br.cataloging_date_gmt as bib_cat_date,
    date(rm.creation_julianday) as creation_date,
    date(rm.record_last_updated_julianday) as record_last_update,
    br.language_code,
    br.bcode1,
    br.bcode2,
    br.bcode3,
    br.is_suppressed as is_bib_suppressed,
    item.volume_record_statement as vol_stmnt,
    item.item_record_num,
    item.creation_date as item_creation_date,
    item.record_last_updated as item_last_update,
    item.agency_code_num as item_agency_code_num,
    item.location_code as item_location_code,
    (
      select
        location_name.name
      from
        location
        join location_name on location_name.location_id = location.id
      where
        location.code = item.location_code
    ) as item_location_name,
    (
      select
        branch_name.name
      from
        location
        join branch on branch.code_num = location.branch_code_num
        join branch_name on branch.id = branch_name.branch_id
      where
        location.code = item.location_code
    ) as item_branch_name,
    item_status_code,
    item.item_format,
    price_cents as item_price_cents
  from
    bib_record as br
    join record_metadata as rm on rm.record_id = br.record_id
    left join item on item.bib_record_num = rm.record_num
)
select
  bib_record_num,
  bib_cat_date,
  creation_date,
  record_last_update,
  language_code,
  bcode1,
  bcode2,
  bcode3,
  is_bib_suppressed,
  vol_stmnt,
  item_record_num,
  item_creation_date,
  item_last_update,
  item_agency_code_num,
  item_location_code,
  item_location_name,
  item_branch_name,
  item_status_code,
  item_format,
  item_price_cents
from
  collection_detail_data;