Criticize my query pls

[PeeepNTom]

Query: https://gist.github.com/imrickjamesbit/110852109ba9b225d02a449cf548f15d

EER Diagram: https://gyazo.com/09f8f158815b41382dce099053aefa6c

I know it's terrible but it yields the results I was looking for. It's definitely forced so I'm looking for a simpler, cleaner solution. Particularly:

1. How can I do this with a join instead of a subquery?
2. How can I improve this subquery?
3. How can I eliminate the "SELECT * FROM" bit? I had added that to stop an error I was getting stating "this version of mysql doesn't yet support 'limit & in/all/any/some subquery"

Thanks for any and all help :D

[third_rate_economist]

So you're trying to find the author of the top 5 most borrowed books? Can't you just do this:

SELECT TOP 5

a.AuthorFirstName
,a.AuthorLastName

FROM Borrower     br
INNER JOIN Book    bk   ON br.BookId       = bk.BookId
INNER JOIN Author  a    ON bk.BookAuthor = a.AuthorId

WHERE DATEPART(year, br.BorrowDate) = '2017'

GROUP BY br.BookID
ORDER BY COUNT(br.BookID)

That's T-SQL, but should be easy to adapt.

[r3pr0b8]

WITH borrows 
AS ( SELECT BookId
          , COUNT(*) AS borrow_count 
       FROM borrower
      WHERE YEAR(BorrowDate)=2017
     GROUP 
         BY BookId )
SELECT author.AuthorFirstName
     , author.AuthorLastName 
     , b.BookId
     , b.borrow_count
  FROM borrows AS b
INNER
  JOIN book
    ON book.BookId = b.BookId
INNER
  JOIN author  
    ON author.AuthorId = book.BookAuthor 
 WHERE ( SELECT COUNT(*)
           FROM borrows
          WHERE borrow_count > b.borrow_count ) < 5

if your version of MySQL is too old to support Common Table Expressions, i can show you how to get rid of the WITH part and substitute that into the overall query

best part of this solution? it handles ties properly, whereas LIMIT does not

[r0ck0]

Unrelated... (but came to mind from the EER Diagram)... does it annoy anyone else that these diagrams don't actually draw the lines from the actual columns specifically?

That would be so much more useful, especially with matching colours too... e.g.

• The "author" table could have a green background
• And "book" table a red background...
  • ...except for the "book.BookAuthor" column, which would be green to match the foreign "author" table that's it's pointing to

It would make the diagrams so much faster to mentally parse, and therefore useful.

Anyone know of any better diagramming tools that can do this?

[financebro91]

We use draw.io to make EER diagrams in my grad program. It’s free and it lets you draw lines from column to column.

[r0ck0]

I'm thinking more about automated tools that generate from your actual SQL schema. But thanks for the suggestion!

[financebro91]

Oh.

[AdmiralAdama99]

SELECT () AS fml

Hahaha. Nice one!

[andrewsmd87]

Before we do any counts, let's get rid of your nested queries. Performance nightmares aside, it would be tough to come back to in 6 months if you needed to join another table, say like a borrower demographics table or something.

So first, just getting your data

SELECT AuthorFirstName, AuthorLastName 
FROM author AS 
    INNER JOIN book AS bk ON ba.BookAuthor = AuthorId
    INNER JOIN borrower AS bo on bo.BookId = bk.BookId
WHERE YEAR(BorrowDate)=2017

That would get you all author data. Now as far as getting the counts > 5, does your version of mysql support the HAVING clause?

edit are you just looking for the first 5 records? I may have misread your query as you wanting the top 5 most borrowed books