Hello,
please what SQL query to write to list all the records from tbl_storage, but instead of showing the SubStorageID integer value, show the storage Name that SubStorageID refers to.
Depends on the database you are using. See status bar of your Base document.
HSQLDB Embedded.
Try out:
SELECT "A".*, "B"."Name" As "Substorage"
FROM "tbl_storage" AS "A" JOIN "tbl_storage" AS "B" ON "A"."Storage_ID"="B"."Storage_ID"I changed the end to “A”.“SubStorageID”=“B”.“StorageID” and it’s working ok (obviously just a mistype). Thank you!
I’m using this query as data for a listbox so i need to select a Bound field (StorageID).
Please how to modify the query for this? I also don’t need fields SubStorageID and Note in the query view, but need location Name from table tbl_location. It’s just too complicated for me right now.
Thank you! Much appreciated!
The query should have 2 columns. Displayed text in first column, the key value in the second.
Bound field = 1 means that the value of the second column is writen to the data field “StorageID”. 1 is an index. 0 would refer to the first column.
SELECT “Name”, “StorageID” FROM “Table” ORDER BY “Name” ASC
Instead of a single name, you may also use a concatenation as in:
SELECT “Surname” || ', ’ || “Forename” AS "Visible, “StorageID” FROM “Table” ORDER BY “Visible” ASC
Had already gone through this here → Base listbox - Table value for default selection - #4 by Ratslinger
I managed to write the query based on your’s and it’s doing what needed. Thank you.
Maybe if someone can tell, how do i keep the formating made on the sql statement (pic above)? When reopening the query for editing in Base, the formatting just lost.
@Ratslinger I got valuable informations from you there, but here my question was more related on expanding the SQL statement itself.
I understood that but my comment (and possibly should have clarified) was in regard to you question of:
which was answered in the previous question.