Basic SQL (Join and Where)

Question

Good night,

I’m trying to solve a problem which is this::

Tables:

editoras (cod int primary key, nome varchar (50));

autores (cod int primary key, nome varchar (50));

livros (isbn char (13), titulo varchar (50), ano_publicacao int,
qtd_estoque int, valor decimal (10,2), id_editora int);
id_editora referencia Editoras

livros_autores (isbn char (13), id_autor int)
isbn referencia Livros
id_autor referencia Autores 

The question asks me to obtain the name(s) (s) of the author(s) who published the cheapest book. Hence, I put the following

select nome from Autores
Inner Join Livros_autores as La 
On  La.id_autor= Autores.cod
Inner Join Livros as Liv 
On La.isbn = Liv.isbn
where valor = (select min(valor) from Livros)

But the name column is not shown :( What do you think is wrong?

Answer 1

To get the cheapest ivro from each author do:

SELECT autores.nome, livros.isbn, MIN(livros.valor) 
FROM autores    INNER JOIN livros_autores ON (autores.cod = livros_autores.id_autor)
                    INNER JOIN livros ON (livros_autores.isbn = livros.isbn)
GROUP BY autores.nome, livros.isbn;

If you only want the cheapest add:

ORDER BY 3 ASC LIMIT 1;

Answer 2

Your select is not ordering the field valor, so just show the name:

select name from Authors
Inner Join Books_authors as La
On La.id_author= Authors.Cod
Inner Join Books as Liv
On La.isbn = Liv.isbn
Where value = (select min(value) from Books)

Add the field in select:

select nome, liv.valor (...)

---

EDIT

As mentioned in the comments, the cheapest book has no corresponding relationship in the table livros_autores, causing the condition of the inner join don’t get answered.

If you wish to display the cheapest book THAT you have author, change the subselect condition. But I do not recommend that.
I believe that the ideal is that each book must have a valid author, since there is no book without author.