SELECT DISTINCT BIB_TEXT.BIB_ID as bib_id, BIB_TEXT.TITLE_BRIEF as title, 
(CASE ITEM_TYPE_NAME
  WHEN 'Book' THEN 'Book'
  WHEN 'RefBook' THEN 'Book'
  WHEN 'DVD' THEN 'DVD'
  WHEN 'Video' THEN 'Video'
  WHEN 'Journal' THEN 'Journal'
  ELSE 'Other'
  END)
AS category
FROM (BIB_TEXT INNER JOIN BIB_MASTER ON BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID) INNER JOIN ((BIB_ITEM INNER JOIN ITEM ON BIB_ITEM.ITEM_ID = ITEM.ITEM_ID) INNER JOIN ITEM_TYPE ON ITEM.ITEM_TYPE_ID = ITEM_TYPE.ITEM_TYPE_ID) ON BIB_MASTER.BIB_ID = BIB_ITEM.BIB_ID
WHERE (((BIB_MASTER.SUPPRESS_IN_OPAC)<>'Y') AND ((BIB_TEXT.LANGUAGE)='fre'))