StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Looks like I solved the first problem. Please, take a look and see if it looks legit (but it feels like it works perfectly):</p> <pre><code>SELECT bc.book_copy_id, subquery.title, subquery.authors, subquery.subject, subquery.language, subquery.publisher FROM book_copy AS bc LEFT JOIN (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) AS subquery ON bc.book_id = subquery.book_id ORDER BY subquery.title </code></pre> <hr> <p>Now I'm done with the second problem. Here's the solution:</p> <pre><code>SELECT subquery.book_id, (COUNT(bc.book_copy_id) - (SELECT COUNT(l.book_copy_id) FROM loan AS l WHERE l.checked_in IS NULL AND l.book_copy_id IN (SELECT bc.book_copy_id FROM book_copy AS bc WHERE bc.book_id = subquery.book_id))) AS available, COUNT(bc.book_copy_id) AS total, subquery.title, subquery.authors, subquery.subject, subquery.language, subquery.publisher FROM book_copy AS bc LEFT JOIN (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) AS subquery ON bc.book_id = subquery.book_id GROUP BY subquery.book_id ORDER BY subquery.title; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload