How to select multiple data from related tables

Question

How to select multiple data from related tables

Navigation

#1 by (1 votes)

0

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

Structure of tables


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 livro(
Id_livro smallint auto_increment,
Titulo_livro varchar(45) not null,
Editora_livro varchar(45),
Quant_livro smallint not null,
primary key(Id_livro)) default charset = utf8; -- criando a tabela de livro

create table emprestimo(
Id_emprestimo smallint not null,
Data_emprestimo Date not null,
Data_devolucao Date not null,
Id_aluno smallint,
Id_livro smallint,
primary key(Id_emprestimo),
foreign key(Id_aluno) references aluno(Id_aluno),
foreign key(Id_livro) references livro(Id_livro)) default charset = utf8; -- Cria a tabela de emprestimo que está refeenciada a tabela aluno e a tabela livro

Now 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;

mysql sql sql-select

asked by anonymous 19.06.2017 / 00:19

1 answer

How to add an external variable in class php Get permission to write to SD Card on Android 6+

score 1 · Answer 1

This query 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 with your query:

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;

You are joining the tables by the wrong attributes. By what you have described, you have to join by the attribute that unites them, which are the foreign keys. So aluno has to be bound to emprestimo by Id_aluno and not Id_emprestimo . Same thing to book.