Trying to explain as simply as I can.
Have a database with employees which have a function code, which is a FK to a Function table describing functions. In the Function table there is a location code which is a FK to a Location table having data on the locations. Linking those together with normal joins is a piece of cake with the Query Builder.
But now consider that e.g. new employees have not been assigned a function code. And I want a query where ALL employees are listed with the names of functions and locations. Those function and location fields would then have to appear blank running the query.
Now a query having employees and functions with a right outer join is again a no brainer. But the adding the location, makes the first outer join apparently working as a normal join. Again using the Query Builder.
I tried breaking the problem up in two pieces by first making a query wit employees - functions (works OK) and then use that query as a left source to llink wit locations again with a right join. And there I get again only the employees with functions assigned.
I checked the SQL code generated by the Query Builder and found that after the correctly formulated right outer join it has another simple where clause on the linking field, filtering out of course the effect of the right join. Simply deleting this where clause makes the query working as desired. But where that additional where clause comes from, beats me.
If that can be reproduced, wouldn’t that be a genuine bug in the Query Builder??
A simple example would help to understand. Please create a database with dummy-data, which will show the behavior of query GUI.
By the way: I only use GUI for choosing the fields I want to get in a query. Then change to SQL and do the rest in SQL directly.
How to create any non-trivial query for LibreOffice Base:
- Click together all the tables and columns you need to be involved.
- Switch to SQL view.
- Paste the code into your preferred code editor.
- Paste back the code into the query window and test it.
In case of non-trivial error, turn on “direct SQL mode”, either because the query will run in this mode or because the error message is more understandable than the one given by Base.