Query One to Many Relationship in Libreoffice Base

[Bloopidy]

          I have the two tables: Hazardous events and controls.

Each hazardous event has up to 3 controls and I have defined relationships between each of the 3 controls in the hazardous event table with the controls table.

When I try do a query for each control for the hazardous event, it only links one of the controls

Is there a way to have the query check each control and output the risk reduction associated with each control for each hazardous event?

I am aware you can do this with Joins but I can only do this if I connect to a MySQL server to my understanding.

Thanks
Best Regards

[Wanderer]

@Bloopidy

you can do this with Joins but I can only do this if I connect to a MySQL server to my understanding.

No, you can use joins, if the connected database can execute them. So with a connected dbase or csv it is not possible.
.
As Base is not able to understand the joins, you need to switch to direct mode in SQL-query editor (rightmost icon). Base is only a front-end and connector. But you can use the full power of your choosen database with SQL.

[Wanderer]

@Bloopidy

Each hazardous event has up to 3 controls

Your design looks not typical for databases. I’d expect perhaps a table to handle n:m-relation between hazard and controls. So someting like a table

HazardId, ControlId, RiskReduction

.
With your design you may need UNION to combine the three SELECT-statements for control1,2,3 and you should be very sure you never need a fourth “control”.

[Bloopidy]

Hi,

Yea, after thinking about it some more, I think setting it up in excel actually works better for my case. Thanks for the help anyway

[Wanderer]

@Bloopidy

setting it up in excel actually works better for my case.

If you have a problem with a database, then no, but if you only reproduce a paper form then a spreadsheet can be sufficient. Good luck.

[nicholas59]

Ok, your intention was

good! But you have to reconsider how to create your file.

Here’s an example.

Hazardous events test.odb (12.6 KB)

[Bloopidy]

Not quite what my intention was, the goal was to have multiple controls per hazard and apply these together for a given hazard. So i want the query to effectively generate a list of all the controls attached to a hazard.

[nicholas59]

I showed you the way… and did rectify your schemes.
Nobody impeaches you to group control types in the risk reduction field.