Dave Jarvis' Repositories

CREATE OR REPLACE VIEW recipe_book.book_photograph_vw AS
-- Get photographs associated with a recipe within a book.
SELECT
  rp.id AS photograph_id,
  rbbr.book_id,
  rrp.recipe_id,
  rp.image_url,
  rp.photograph_category_id,
  rpc.label AS photograph_category
FROM
  recipe.photograph rp,
  recipe.recipe_photograph rrp,
  recipe_book.book_recipe rbbr,
  recipe.photograph_category rpc
WHERE
  rp.id = rrp.photograph_id AND
  rrp.id = rbbr.recipe_id AND
  rp.photograph_category_id = rpc.id AND
  rpc.classification = 'RECIPE'
UNION ALL
-- Get photographs associated with a book, but not a recipe.
SELECT
  rp.id AS photograph_id,
  rbbp.book_id,
  NULL AS recipe_id,
  rp.image_url,
  rp.photograph_category_id,
  rpc.label AS photograph_category
FROM
  recipe.photograph rp,
  recipe_book.book_photograph rbbp,
  recipe.photograph_category rpc
WHERE
  rp.id = rbbp.photograph_id AND
  rp.photograph_category_id = rpc.id AND
  rpc.classification = 'BOOK'
UNION ALL
-- Get the recipe photographs that aren't part of a book (yet).
SELECT
  rp.id AS photograph_id,
  NULL AS book_id,
  rrp.recipe_id,
  rp.image_url,
  rp.photograph_category_id,
  rpc.label AS photograph_category
FROM
  recipe.photograph rp,
  recipe.photograph_category rpc,
  recipe.recipe_photograph rrp
WHERE
  rrp.photograph_id = rp.id AND
  rp.photograph_category_id = rpc.id AND
  NOT EXISTS(
    SELECT
      recipe_id
    FROM
      recipe_book.book_recipe rbbr
    WHERE rbbr.recipe_id = rrp.recipe_id 
  );