How to select data that depends on other tables that have another key in common?

Question

Hello! I was working on a database project using the SQL language in Mysql Workbench. The setting of my project is a university library where we have the tables: (Bold = PK | Italic = FK)

• Emprestimo(ID_emprestimo, ID_funcionario, data_emprestimo)

• Emprestimo_Livro(ID_emprestimo, ID_livro, data_devolucao_livro)

• Livro(ID_livro, titulo, ISBN, formato, edicao, ano_da_edicao, paginas, ID_acervo, ID_editora)

• Emprestimo_Trabalho_Academico(ID_emprestimo, ID_trabalho_academico, data_devolucao_trabalho_academico)

• Trabalho_Academico(ID_trabalho_academico, titulo, ano_de_publicacao, matricula_professor, ID_acervo)

• Acervo(ID_acervo, campus)

I would like to select information from these tables: "All loans from a collection". These are the only tables that relate to the keys I need to reach the loans of materials belonging to a collection.

The table Emprestimo contains all loans made while the tables Emprestimo_Livro and Business & Workingacademic loans of their respective materials and the identity of these materials.
In turn, the tables Livro and Trabalho_Academico have the primary key of Acervo, which is the collection from where they belong. I was trying to select all the materials from a particular collection in this way (but without success):

SELECT Emprestimo.ID_emprestimo
    FROM 
        Emprestimo
    INNER JOIN
        emprestimo_livro ON Emprestimo.ID_emprestimo = emprestimo_livro.ID_emprestimo
    INNER JOIN
        emprestimo_trabalho_academico ON Emprestimo.ID_emprestimo = emprestimo_trabalho_academico.ID_emprestimo
    INNER JOIN
        Livro ON Livro.ID_acervo = Acervo.ID_acervo
    INNER JOIN
        Trabalho_Academico ON Trabalho_Academico.ID_acervo = Acervo.ID_acervo
    WHERE
        Acervo.ID_acervo = 'C100';

Now I’m out of ideas, I don’t know how to select the book loans and academic works that belong to the same collection. Any help will be greatly appreciated! Thank you!

Answer 1

Okay, in this case I believe it helps to think with relationships.

Some banks allow us to create relationships using the WITH statement, that way we can do something like this:

WITH items_acervos AS (
    SELECT Livro.ID_acervo, 
           Livro.titulo,
           Livro.ID_livro referencia,
           'LIVRO' tipo,
           Emprestimo_Livro.ID_emprestimo
    FROM Livro
    JOIN Emprestimo_Livro ON Livro.ID_livro = Emprestimo_Livro.ID_livro
    UNION 
    SELECT Trabalho_Academico.ID_acervo, 
           Trabalho_Academico.titulo,
           Trabalho_Academico.ID_trabalho_academico referencia,
           'TRABALHO ACADEMICO' tipo,
           Emprestimo_Trabalho_Academico.ID_emprestimo
    FROM Trabalho_Academico
    JOIN Emprestimo_Trabalho_Academico ON Trabalho_Academico.ID_trabalho_academico = Emprestimo_Trabalho_Academico.ID_trabalho_academico
)
SELECT Emprestimo.ID_emprestimo,
       Emprestimo.ID_funcionario,
       items_acervos.tipo,
       items_acervos.titulo,
       items_acervos.referencia
FROM Emprestimo 
JOIN items_acervos ON items_acervos.ID_emprestimo = Emprestimo.ID_emprestimo
JOIN Acervo ON Acervo.ID_acervo = items_acervos.ID_acervo AND 
               Acervo.ID_acervo = 'C100'

In this case what we did was create a relationship that represents the loans of the items lent, regardless of whether they are books or academic works, in this list we include information common to both entities, such as title, reference id and a column to inform the type.

Having this relationship we can select with a JOIN with the Loan table to get the employees and with the Collection table if we want to bring some more information, such as the name of the collection, category, etc.

Consideration

One positive point of this approach is that if in the future, for example, you come to have a set of tables like Material_AudioVisual and Emprestimo_Material_AudioVisual you can add a select for them within the relation items_acervos, without having to modify the rest of the query.