Doubt Inner Join, left Join mysql

Asked

Viewed 188 times

3

I am working with the following tables:

CREATE TABLE livro
(     Id_livro int NOT NULL,
      Nome_livro CHAR(40) NOT NULL,
      Pg int,
      Edicao int, 
      Editora int NOT NULL,
      Assunto int NOT NULL, 
      Preco decimal (5,2) NOT NULL,
      PRIMARY KEY (Id_livro),
      FOREIGN KEY (Editora) REFERENCES Editora(Id_editora),
      FOREIGN KEY (Assunto) REFERENCES Assunto(Id_assunto)
) Engine=InnoDB;


CREATE TABLE Emprestimo
(   operacao int NOT NULL,
    data_emp date NOT NULL,
    data_dev date NOT NULL,    
    cliente int, 
    livro_1 int NOT NULL, 
    livro_2 int, 
    PRIMARY KEY (operacao),
    FOREIGN KEY (cliente) REFERENCES Cliente(Id_cliente), 
    FOREIGN KEY (livro_1) REFERENCES livro(Id_livro), 
    FOREIGN KEY (livro_2) REFERENCES livro(Id_livro)
) Engine=InnoDB;

What I want to know is the number of times each book has been rented. For this I am making the consultation

select l.Nome_livro, count(emp.livro_1) as 'livro 1', count(emp2.livro_2) as 'livro 2'
from livro l 
JOIN emprestimo emp ON emp.livro_1 = l.Id_livro
left join emprestimo emp2 ON emp2.livro_2 = l.Id_livro
group by l.Nome_livro;

But the result seems to be multiplying the column emp2.livro_2 for emp.livro_1. For example, this is the output I receive, when in fact the last line should be 4 and 1 and the penultimate line 3 and 1. Already the line Os Miseráveis should be 3 and 2 respectively: inserir a descrição da imagem aqui

mysql join

  • I don’t really understand why you created two relationships with the same table. But I created your tables, filled in and made a single left and worked correctly: select l.Nome_livro, count(emp.livro_1) as 'livro 1'
from livro l 
LEFT JOIN emprestimo emp ON emp.livro_1 = l.Id_livro
group by l.Nome_livro;. Test this and better detail why these duplicate relationships.

  • @Intelidersistemas I created 2 relationships because I want to make the First Record books Count and also the Books Count stored in the second record.

  • This first record refers to the loan and the second to the return, because if it is not has not much sense, you have to have a parameter in each record to be able to do this, for example: the first item is loan the second of return. if you can provide more details thank you.

  • The 2 are on loan. Each user can pick up 2 books per operation. So I record book 1 and book 2. So the second record may be null a few times. I appreciate the help.

  • What identifies the 2nd loan !?

1 answer

1


For anyone who might be wanting the answer, I made the solution not with inner or left join, but rather with Union 

SELECT Nome_livro, count(livro_1) as 'mais retirados'
from (select nome_livro, livro_1 
    FROM emprestimo, livro
    where emprestimo.livro_1 = livro.Id_livro
    UNION ALL
    SELECT nome_livro, livro_2
    From emprestimo, livro
    where emprestimo.livro_2 = livro.Id_livro) as x
group by Nome_livro;

Browser other questions tagged mysql join

You are not signed in. Login or sign up in order to post.