List most borrowed books

Question

List most borrowed books

Navigation

#1 by (3 votes)

2

I would like to list the books in order of more borrowed, but there is no book that was borrowed so it shows none. I would like it to show in order but even if the book has not been borrowed once it has to be listed.

SELECT Livros.Nome, COUNT(Emprestimo.Livro_IDLivro) FROM Livros 
INNER JOIN Emprestimo ON Livros.IDLivro = Emprestimo.Livro_IDLivro 
ORDER BY COUNT(Emprestimo.Livro_IDLivro) ASC;

sqlite

asked by anonymous 29.03.2017 / 16:58

1 answer

How do I define custom sort on a DB return? Function calculates to some extent and for

score 3 · Accepted Answer

To list all of the books, you need to use LEFT JOIN instead of INNER JOIN , since LEFT will fetch all records from the table to the left (first table).

To show what you have more for what you have, you need to use DESC on your ORDER BY

SELECT Livros.Nome, COUNT(Emprestimo.Livro_IDLivro) FROM Livros 
LEFT JOIN Emprestimo ON Livros.IDLivro = Emprestimo.Livro_IDLivro 
ORDER BY COUNT(Emprestimo.Livro_IDLivro) DESC;