I'm trying to filter the selection in a list box in LibreOffice Base so that if an item (from a separate table) has already been chosen, it won't be available to be selected again.
As for how my database is designed, I have a main table and multiple category tables. I want a form for each category so that I can add the items from the main table to the categories without the list box suggesting already assigned items.
Right now I'm using a form where the table to be edited is just shown as a table. I designed the query for the list box such that it only selects table rows which aren't in the table yet. But of course it won't show already created entries as the query returns nothing for them.
Here is a minimal code example of what I was trying:
SELECT "ID"
FROM "Main"
WHERE "ID" NOT IN
(SELECT "ID"
FROM "Category1"
UNION
SELECT "ID"
FROM "Category2")
So far I'm guessing that I'd need a different approach and that my problem can't be solved with the query alone.
