Libreoffice Base: How to filter and search in multiple tables? [closed]
Hello.
Using the definition of Libreoffice Base manual (https://documentation.libreoffice.org... p. 198-201), I need to build a query for filtering/searching data in multiple tables, avoiding information repetition.
I am using version 6.0.7.3 of Libreoffice Base, with the default SQL language (HSQLDB).
DATABASE DESCRIPTION
I am building a database for some donations to an archive.
Each donation (“Incorporações”) has the following fields:
- the donation’s primary key (“ID_incorporação”);
- the name of the entity that donated the documents (“Nome_entidade”);
- a brief description of the donation (“Descrição_incorporação”);
- the oldest known year in which the documents of the donation were produced (“Ano_inicial_incorporação”);
- the newest known year in which the documents of the donation were produced (“Ano_final_incorporação”).
Each donation is divided into series. The series itself are defined by a table (“Séries”) that has the following fields:
- the serie’s primary key (“ID_série”);
- the type of the serie (“Nome_série”).
The relation of the series with the donations (that has a many-to-many nature) is represented in a table (“Rel_incorporação_série”) that contains:
- its unique primary key (“ID_rel_incorporação_série”);
- the donation’s foreign key (“ID_incorporação”);
- the serie’s foreign key (“ID_série”);
- a full description of the donation’s documents belonging to that serie (“Descrição_série”).
Each series within the donation also have some keywords attached. The keywords, in an identical way to the series, are defined by a table (“Assuntos”) that has the following fields:
- the keyword’s primary key (“ID_assunto”);
- the name of the keyword (“Nome_assunto”);
- the type of the keyword (“Temática”);
- a description of the keyword (“Descrição”).
Finally, I have made a table for recording which keywords each series has (“Rel_série_assunto”) that contains only two fields, defining, together, the table’s primary key:
- the foreign key regarding the relationship of the serie with the donation (“ID_rel_incorporação_série”);
- the foreign key of the keyword (“ID_assunto”).
I have placed a picture of all these tables with all the relationships.
PROBLEM DESCRIPTION
- I want to filter the various series that are contained in the donations (any field in "Rel_incorporação_série" table) by its keywords ("ID_assunto").
- I want to search for words in the series’ and in the donation’s descriptions ("Descrição_incorporação" in "Incorporações" table and "Descrição_série" in "Rel_incorporação_série" table), ideally using only one field as the search criteria.
- I want to search for words in the entities that donated the documents ("Nome_entidade" in "Incorporações" table).
- I don’t want any repetition of information.
Using a filter table, for storing the filter/search criterias, I created a form for this purpose. I am trying to make one big query, using the examples given in the refered pages of the manual, showing all the information on a subform through a single table. The problem with that solution is that the table captures all the repetitions, regarding how much series I have in a donation, but must important, how much keywords I have in each serie.
Here is the query I tried. "Tabela_filtro" refers to the filter table, while "Nome_entidade", "Descrições" and "ID_assunto1" to "ID_assunto4" to the fields contained in ...
Relationship definition in the query search for that in your help link