How to select multiple data from related tables

Question

I created three tables in the student database, book and loan, I would like to list all the books that were loaned to each student, however, when the student borrows more than one book the command I use to search the bank only returns one of them, and not all books that were lent to students

Table structure

create table aluno(
Id_aluno smallint auto_increment,
Nome_aluno varchar(45) not null,
Cpf_aluno varchar(11) not null,
Curso_aluno varchar(45) not null,
primary key(Id_aluno)) default charset = utf8; -- Criando a tabela aluno

create table book( Id_livro smallint auto_increment, Titulo_livro varchar(45) not null, Editora_livro varchar(45), Quant_livro smallint not null, Primary key(Id_book)) default charset = utf8; -- creating the book table

create table emprestimo( Id_emprestimo smallint not null, Data_emprestimo Date not null, Data_devolucao Date not null, Id_student smallint, Id_smallint book, Primary key(Id_emprestimo), Foreign key(student id_student) pupil references(student id_student), Foreign key(Id_book) Book references(Id_book)) default charset = utf8; -- Creates the loan table that is refocused on the student table and the book table

The command I was using to search is as follows:

select Nome_aluno, Titulo_livro from emprestimo inner join aluno on aluno.Id_aluno = emprestimo.Id_emprestimo inner join livro on livro.Id_livro = emprestimo.Id_emprestimo;

Answer 1

This consultation should work:

SELECT livro.Titulo_livro, aluno.Nome_aluno
FROM emprestimo JOIN livro ON emprestimo.Id_livro = livro.Id_livro
JOIN aluno ON emprestimo.Id_aluno = aluno.Id_aluno

The problem of your consultation:

select Nome_aluno, Titulo_livro 
from emprestimo inner join aluno on aluno.Id_aluno = emprestimo.Id_emprestimo inner join livro on livro.Id_livro = emprestimo.Id_emprestimo;

is that you are joining the tables by the wrong attributes. From what you described, you have to join by the attribute that joins them, which are the foreign keys. Soon aluno has to be linked to emprestimo for Id_aluno and not Id_emprestimo. Same thing for book.