title: MySQL: joining a lot of tables
body: <p>I have these tables in my MySQL database for a simple college library application (I simplified them a little bit for obvious reasons):</p>
  
  <pre><code>SELECT * FROM language;
  ╔═════════════╦══════════╗
  ║ language_id ║ language ║
  ╠═════════════╬══════════╣
  ║          11 ║  English ║
  ║          12 ║  Russian ║
  ║          13 ║   German ║
  ╚═════════════╩══════════╝
  
  SELECT * FROM subject;
  ╔════════════╦═════════════╗
  ║ subject_id ║   subject   ║
  ╠════════════╬═════════════╣
  ║         21 ║ Mathematics ║
  ║         22 ║     History ║
  ║         23 ║   Chemistry ║
  ║         24 ║     Physics ║
  ╚════════════╩═════════════╝
  
  SELECT publisher_id, publisher FROM publisher;
  ╔══════════════╦═══════════════╗
  ║ publisher_id ║   publisher   ║
  ╠══════════════╬═══════════════╣
  ║           31 ║ Berkley Books ║
  ║           32 ║       Penguin ║
  ╚══════════════╩═══════════════╝
  
  SELECT author_id, first_name, last_name FROM author;
  ╔═══════════╦════════════╦═══════════╗
  ║ author_id ║ first_name ║ last_name ║
  ╠═══════════╬════════════╬═══════════╣
  ║        41 ║        Joe ║    Schmoe ║
  ║        42 ║       John ║     Smith ║
  ╚═══════════╩════════════╩═══════════╝
  
  SELECT book_id, title, language_id, subject_id, publisher_id FROM book;
  ╔═════════╦═══════╦═════════════╦════════════╦══════════════╗
  ║ book_id ║ title ║ language_id ║ subject_id ║ publisher_id ║
  ╠═════════╬═══════╬═════════════╬════════════╬══════════════╣
  ║       1 ║     A ║          11 ║         22 ║           31 ║
  ║       2 ║     B ║          13 ║         24 ║           32 ║
  ╚═════════╩═══════╩═════════════╩════════════╩══════════════╝
  
  SELECT book_id, author_id FROM book_author;
  ╔═════════╦════════════╗
  ║ book_id ║ author_id  ║
  ╠═════════╬════════════╣
  ║       1 ║         41 ║
  ║       1 ║         42 ║
  ║       2 ║         41 ║
  ╚═════════╩════════════╝
  
  SELECT book_copy_id, book_id FROM book_copy;
  ╔══════════════╦═════════╗
  ║ book_copy_id ║ book_id ║
  ╠══════════════╬═════════╣
  ║            1 ║       1 ║
  ║            2 ║       1 ║
  ║            3 ║       1 ║
  ║            4 ║       2 ║
  ║            5 ║       2 ║
  ╚══════════════╩═════════╝
  </code></pre>
  
  <p>I have one query that looks like this (I'm showing it to you just in case):</p>
  
  <pre><code>SELECT b.book_id,
         b.title,
         GROUP_CONCAT(CONCAT_WS(' ', a.last_name,
                                     a.first_name)
                                ORDER BY a.last_name
                                SEPARATOR ', ') AS authors,
         l.language,
         s.subject,
         p.publisher
  FROM   book AS b
         LEFT JOIN book_author AS ba
                ON b.book_id = ba.book_id
         LEFT JOIN author AS a
                ON ba.author_id = a.author_id
         LEFT JOIN language AS l
                ON b.language_id = l.language_id
         LEFT JOIN subject AS s
                ON b.subject_id = s.subject_id
         LEFT JOIN publisher AS p
                ON b.publisher_id = p.publisher_id
  GROUP BY b.title ASC;
  
  ╔═════════╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
  ║ book_id ║ title ║         authors        ║ language ║ subject ║   publisher   ║
  ╠═════════╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
  ║       1 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
  ║       2 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
  ╚═════════╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝
  </code></pre>
  
  <p>Now, I would like to have this selection:</p>
  
  <pre><code>╔══════════════╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
  ║ book_copy_id ║ title ║         authors        ║ language ║ subject ║   publisher   ║
  ╠══════════════╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
  ║            1 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
  ║            2 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
  ║            3 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
  ║            4 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
  ║            5 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
  ╚══════════════╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝
  </code></pre>
  
  <p>I also have these two tables:</p>
  
  <pre><code>SELECT student_id, first_name, last_name FROM student;
  ╔════════════╦════════════╦═══════════╗
  ║ student_id ║ first_name ║ last_name ║
  ╠════════════╬════════════╬═══════════╣
  ║         81 ║        Bob ║     Dylan ║
  ║         82 ║        Jim ║    Carrey ║
  ╚════════════╩════════════╩═══════════╝
  
  SELECT student_id, book_copy_id FROM loan;
  ╔════════════╦══════════════╗
  ║ student_id ║ book_copy_id ║
  ╠════════════╬══════════════╣
  ║         81 ║            1 ║
  ║         81 ║            4 ║
  ║         82 ║            5 ║
  ╚════════════╩══════════════╝
  </code></pre>
  
  <p>I need to write a query that produces this selection:</p>
  
  <pre><code>-- 'a' stands for 'the quantity of book copies that are available for a loan'
  -- 't' stands for 'the total quantity of book copies'
  ╔═══╦═══╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
  ║ a ║ t ║ title ║         authors        ║ language ║ subject ║ publisher     ║
  ╠═══╬═══╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
  ║ 2 ║ 3 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
  ║ 0 ║ 2 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
  ╚═══╩═══╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝
  </code></pre>
  
  <p>I hope I have been quite explicit outlining it with diagrams. Please, hemp me out with writing queries for these two problems.</p>