WIN 10, LO 7.2.3 , HSQL 2.61
Generic query design question involving listbox bound value in underlying table
Table x has a field “Region” - Integer
Value comes from listbox in form A
A multitable query provides the data for form B
If the Region field of a record in Table x has no value, the record will not show.
COALESCE will not work.
Is there some other way to display the record with a missing Region ?
Thanks
Hello,
Based upon provided information, you are trying to display records from a query on Form B. The problem seems to be that if the Region field is empty, there is no record in the query?
.
Would need at least your query definition and possibly the table definitions to help determine what is happening.
The point is: Why?
I assume all ideas I may have will either also not work, or maybe you made a mistake with the statement.
Without your actual query /attempt via COALESCE there is only guesswork possible.
You may rewrite your query using CASE instead of COALESCE - maybe you see a solution in this syntax better…
@maya1 : Seems you connect two tables like
SELECT "tableA".*, "tableB".* FROM "tableA", "tableB" WHERE "tableA"."RegioID" = "tableB"."ID"
This will show only values from “tableA” and “tableB” where “tableA”.“RegioID” isn’t NULL.
SELECT "tableA".*, "tableB".* FROM "tableA" LEFT JOIN "tableB" ON "tableA"."RegioID" = "tableB"."ID"
Will show all rows of “tableA”.
