I have three tables: table1, table2 and table3. Table1 has the fields: “id” and “names of table1”; table2 has the fields “id” and “names of table 2”; and the table3 has the fields: “id”, “name”, “id_FK_table1” and “id_FK_table2”, the field “id_FK_table1” is a foreign key of table1.id and “id_FK_table2” is a foreign key of table2.id.
I have a form with two “table control”, the first “table control” is link with table1 and the second “table control” is link with table2. I wish I had a “text box” that shows me the “name” field of table3, taking into account the records selected in the two previous “table controls”.
Thanks in advance
Please note in question(s) when posting in multiple locations. This eliminates duplicate effort when/if a question has been addressed.
Also posted here:
Form One to many relationships between two tables using int
You should note your posting here in that question as well.
Hello,
Have looked at both questions ( other → https://ask.libreoffice.org/en/question/274481/base-one-to-many-relationships-between-two-tables-using-intermediate-table/) and do not see a reason for a third table.
Tables in this question:
Table1 has the fields:
"id" and "names of table1";
table2 has the fields
"id" and "names of table 2";
table3 has the fields:
"id", "name", "id_FK_table1" and "id_FK_table2
Tables in other question:
Table1 has the following fields:
"id_t1" and "names of t1";
table2 has the following fields:
"id_t2" and "names_t2";
table3 has the following fields:
"id_t3", "id_t1" and "id_t2"
The difference being "name in table 3 - not known what if any bearing this plays.
In a one-to-many situation, table 3 is not needed (again with “name’” eliminated). Construction would be:
Table1 has the following fields:
"id_t1" and "names of t1";
table2 has the following fields:
"id_t2", "link_id_t1" and "names_t2";
where "link_id_t1" is the primary key of table 1 where this record is linked to. A third table would be needed if there were a many-to-many situation.
Please comment if I have missed something in your questions.
Edit:
First I will state, you are much better off in re-doing what you have. Using the three table set-up as you have noted in your comment is prone to errors. If table 1 is say family names and table 3 has members of each family, table two is allowing a many-to-many condition. You could have the same family member in many different families. Can create a lot of problems.
But, as you have requested, here is a sample of what you wanted:
In the only form, Table 1 is the family. Selecting will show all table 3 links. Any selection in table 3 will show table 2 linked item. For ease of form creation I used all table controls. Table 2 could easily be a text box instead.
This set-up is not conducive to entering, modifying or deleting records. Please reconsider.
@Ratslinger It is true. Excuse me, I’m learning at the moment, but the database is already designed and I can’t modify it, I just have to make the form.
I would be very grateful if you could help me, considering that there is table3
Already stated but again best to change to proper one-to-many. Again if you insist on keeping what you have, you can make the link to table 2 in table 3 UNIQUE. See my answer in this post → Base - remove field unique constraint
This statement worked for me on the test Base file posted:
ALTER TABLE "Table3" ADD CONSTRAINT t2_unique UNIQUE ("id_t2")