Problem with SQL Code for List Box on Subform (for a one-to-many table)

[DMG]

Hello. I am learning LibreOffice Base version 7.5 (64k) on a Windows 10 desktop for a database that is HSQLDB embedded.
.
Although I understand how to do a simple list box on a form, I do NOT understand how to create a list box for a subform that is linked to a one-to-many table.
.
For example, here are my database table relationships:

• tblRecipes is behind the Main Form.
• tblIngredients is a catalog of all ingredients.
• tblRecipeIngredients is linked between the above two tables in one-to-many relationships, and contains a Primary Key that references the list of ingredients that is included in each recipe.
  .
  
  

My form is designed with tblRecipes as the main form, and has a SUBform of its ingredients via tblRecipeIngredients.
.
As you can see below, the “Ingredients” field lists the NUMBER of the fldRecipeIngredients Primary Key (in tblRecipeIngredients)… but I need to see the NAME of the ingredient as defined in tblIngredients.

I am a Newbie, and have tried the following SQL Code, but it does NOT work. I also tried changing the “Bound” field to “0” to match the ID field to the Primary Key for the Data field," but that didn’t work either.

Does anyone know how to fix my SQL Code to display the NAME of the ingredient stored in tblIngredients, and referenced on the subform’s tblRecipeIngredients one-to-many table for each recipe?
.
I’d like to see the following that allows me to select the ingredient from a drop-down list:

Thanks!

[Ratslinger]

Hello,
Your list box SQL is backwards. First selected item is what is displayed and second item can be what is stored.
.
So to display the name and store the ID it is:

Select "fldIngredientName", "ID" from "tblIngredients"

.
and Bound field is 1 (field number in select statement relative to zero).

[DMG]

@Ratslinger

Select "fldIngredientName", "ID" from "tblIngredients"

THANK YOU, @Ratslinger
It now displays the ingredient names for all EXISTING recipes in the database.
.
BUT it won’t let me add all the ingredients for a NEW recipe without throwing the following error message:

Also… the column next to the list box (fldRecipeIngredients) is the original ID field that I was trying to replace with the List Box. fldRecipeIngredients stores the unique ID for tblRecipeIngredients, and the next autovalue in that table should be 9303 (not 8).

Do you know what’s wrong?

[Ratslinger]

@DMG
The sub form appears to be entries for tblRecipeIngredients. You seem to be wanting to used the tblIngredients ID as a primary key. A primary key must be unique in a table and this ID would not be. Cannot be used as a primary key.
.
Edit:

Also do not understand image as it shows <AutoField> for the list box field - should never be.
.
Isn’t the key for this sub form fldRecipeIngredients? Have you assigned the the correct field to the column? Base sample should help.

[DMG]

My subform is tied to the tblRecipeIngredients table, which is joined between tblIngredients and tblRecipes.
.
The field fldRecipeIngredients is an autovalue primary key field because every time I add an ingredient for a new recipe it should increment with a unique ID for that particular ingredient in that particular recipe.
.
Yet I want to display (and choose via drop-down) the fldIngredientName field that is stored in the tblIngredients table.
.
This is the way it is set up in my MS Access database, and I’m trying to duplicate this using BASE:
.

.

I was going to upload the database so you could see it, but when I tried to delete the over-1000 records in this database, I got the following error:
.

[Ratslinger]

@DMG
Is this representative of what you want? → RecipeSample.odb (13.2 KB)

[Villeroy]

https://forum.openoffice.org/en/forum/download/file.php?id=11250 (one-to-many, many-to-many)

[DMG]

@Ratslinger This is getting much, much closer… THANK YOU!
.
For the list box, you used the fldRI_IngredientID (my foreign key that ties to tblIngredients), whereas I had incorrectly used fldRecipeIngredients (the primary key in tblRecipeIngredients… I’m not sure why that worked in Access).
.
But I’m still getting the “Integrity constraint violation” error message when I try to delete some of the records from tblRecipes. I googled “Integrity Constraint Violation” and it said there may be a problem with my table relationship. But this is the same table relationship I used in Access.
.

[Ratslinger]

@DMG
It turns out I had not paid close enough attention to your design. As stated by @UnklDonald and @Villeroy you are set as a many-to-many.
.
Please take a look at their responses and sample.

[DMG]

@Villeroy Thank you! I downloaded your sample database. Give me a second to look through everything. I can already see in your Table Relationships that you have set up your intersection tables the same way @UnklDonald suggested.

[UnklDonald]

Per your design, tblRecipeIngredients is an intersection table reflecting a many to many relationship between tblRecipes and tblIngredients. More than one ingredient can appear in more than one recipe.
A one to many relationship could have multiple ingredients in a given recipe but each ingredient could then appear in only one recipe.

Typically, the Primary Key for an intersection table is a composite of two foreign key fields, in your case fldRI_RecipeID and fldRI_IngredientID. That would ensure that each ingredient in a recipe is unique and fldRecipeIngredients would not be needed.

Your design does allow the same ingredient to appear more than once in a given recipe and since fldRecipeIngredients is a unique auto-generated Integer value it is simply an index that allows the database engine to uniquely identify that record and should never change.

To avoid there error message you got when you tried to delete records from tblRecipes,
you must first delete records from tblRecipeIngredients where the value(s) in fldRI_RecipeID matches the fldRecipeID value(s) you want to delete from tblRecipe.

[DMG]

@UnklDonald Thank you for your reply.
.
That does make sense, but don’t you need fldRecipeIngredients to represent the composite of the foreign keys?? I used fldRecipeIngredients in my Access database, and it worked… but that method doesn’t work in Base.
.
Sometimes (although rarely), I do want to use the same ingredient twice in a recipe because it may be used more than once in different stages of the same recipe.
.
Per Ratslinger’s suggestion (via sample database), I used the foreign key fldRI_IngredientID, and that did work.
.
As for the “Integrity constraint violation” error message. When I try to delete this same record from my Access database, it alerts me that there are cascading records that will also be deleted, and asks whether I’m sure I want to delete… I just choose “Yes” and Access deletes.

.
I’m such a Newbie at this, I’m sure there is something I just don’t understand.

[DMG]

@UnklDonald After looking at the table relationships in @Villeroy Sample database, I think I understand my problem. It appears that to concatenate the two foreign keys in the tblRecipeIngredients, I must designate BOTH foreign keys as TWO Primary Keys… correct?
.

.
But when I right click on the second foreign key in table design view and choose “Primary Key,” it DESELECTS the first Primary Key status of the first foreign key. How do you select TWO at the same time?

[DMG]

Regarding the error message when I try to delete records in the table tblRecipes… I just found the setting for updating and deleting relationship fields (what MS Access called “Cascading” fields). This will let me delete the records… unless there is some reason I should NOT select these two options (???).
.

[Villeroy]

Ctrl+Click selects multiple items. Select two fields, right-click > Primary key

[DMG]

Ah! I was doing “Shift”+Click. THANK YOU, @Villeroy !
.
So… is it EVER a good idea to design a database with one unique Primary Key & 2 foreign keys (eg., ability to add the SAME item twice, with different quantities). … OR… should I AWAYS have only 2 foreign keys to avoid future problems?

[Villeroy]

Ctrl+Click performs a multiple selection, Shift+Click performs a from…until selection in file managers, office suites and all kinds of desktop software where multiple items are selectable.

[Villeroy]

Use an automatic integer as primary key when you need a list where you can append new items without bothering about the key. The database engine will add unique numbers as needed.
In a many-to-many relation the linking table maps the existing items of one table to the existing items of another table. The two numbers of a new record already exist in the other tables before you add a new record.
Alternatively you could build the mapping table with its own automatic primary key and the 2 foreign keys as separate fields.

[Persons.ID] 1--> n [P_T.PID]
                    [P_T.TID] n <--[Things.ID]
                [P_T.AUTO_ID]

This setup would allow duplicate combinations of things and persons.

The original setup where the two columns are two foreign keys and one primary key do not allow duplicate combinations as you will notice when you try to add another item “Hammer” to person “John Doe” or another “John Doe” to item “Hammer”

[DMG]

Thank you!

[UnklDonald]

Yes, there is some adjustment when switching from Access to Base. Base has a much smaller footprint than Access, so a number of things that are done automatically in Access require your attention in Base.

The Delete Cascade option is exactly what you need to avoid the error message. It accomplishes the same task a cascading delete in Access.

As you mentioned, some recipes call for the same ingredient in different stages, so the single Primary Key with two Foreign Keys is correct for that situation.