My Personal Library

Formatted SQL Query

      SELECT
          book.bookID, 
          book.title, 
          (CASE WHEN SUBSTRING_INDEX(book.title, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT(
              SUBSTRING(
                  book.title,
                  INSTR(book.title, ' ')+ 1
              ),
              ', ',
              SUBSTRING_INDEX(book.title, ' ', 1)
          ) ELSE book.title END) AS titleSort,
          book.title_remainder,
          book.title_original,
          GROUP_CONCAT(
              CASE WHEN auth.role = 'author' THEN CONCAT(
                  auth.name_first, ' ', auth.name_last
              ) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS author,
          GROUP_CONCAT(
              CASE WHEN auth.role = 'author' THEN CONCAT(
                  auth.name_first
              ) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS authFirst,
          GROUP_CONCAT(
              CASE WHEN auth.role = 'author' THEN CONCAT(
                  auth.name_last
              ) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS authLast,
          GROUP_CONCAT(
              CASE WHEN ed.role = 'editor' THEN CONCAT(ed.name_first, ' ', ed.name_last) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS editor, 
          GROUP_CONCAT(
              CASE WHEN trnsl.role = 'translator' THEN CONCAT(
                  trnsl.name_first, ' ', trnsl.name_last
              ) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS translator, 
          GROUP_CONCAT(
              CASE WHEN art.role = 'artist' THEN CONCAT(
                  art.name_first, ' ', art.name_last
              ) ELSE NULL END
              ORDER BY
                  person_ordinal ASC SEPARATOR ', '
          ) AS artist, 
          book_series.series_title, 
          book.volume, 
          book.edition, 
          publisher.publisher, 
          publisher.address, 
          publisher.city, 
          publisher.state, 
          publisher.country, 
          book.language, 
          book.summary, 
          book.form, 
          book.date_of_pub, 
          book.pages, 
          book.ISBN, 
          book.OCLC 
      FROM
          bibrecord 
          INNER JOIN (
              book 
              LEFT JOIN publisher ON book.pubID = publisher.pubID 
              LEFT JOIN book_series ON book.seriesID = book_series.seriesID
          ) ON bibrecord.bookID = book.bookID 
          LEFT JOIN person AS auth ON bibrecord.personID = auth.personID 
          LEFT JOIN person AS ed ON bibrecord.personID = ed.personID 
          LEFT JOIN person AS trnsl ON bibrecord.personID = trnsl.personID 
          LEFT JOIN person AS art ON bibrecord.personID = art.personID 
      GROUP BY
          bibrecord.bookID 
      ORDER BY
          $sortBy $order,
          titleSort $order,
          book.seriesID $order, 
          book.volume $order
      LIMIT
          $offset, 
          $items_per_page