Description: Context: I'm working on setting up an anamnesis database for a medical institution. My test-database needs to display certain disorders/diseases in one table and associated risk situations in another. Relevant Tables (if needed I can provide a graph of the database-design): - hämo_erkrankung: id(int), lokalisation(int), person(int), zeitpunkt(date) [eng: time of occurence] FK: person -> person.id lokalisation -> lokalisation.id (one individual disease/disorder per row) - situation: id(int), situation(varchar) (one (risk-)situation per row) - situation_p: id(int), hämo(int), situation(int) FK: hämo -> hämo_erkrankung.id situation -> situation.id (n risk situations for one disease / disorder referenced by "hämo") Problem: I wanted to create a Query displaying the actual situation associated with a disease/disorder rather than the foreign key, using this SQL-Command: SELECT "SP"."id", "SP"."hämo", "S"."situation" FROM "situation_p" "SP", "situation" "S" WHERE "SP"."situation" = "S"."id" The desired result table is returned in Postgres (using the Terminal). However, in Base the "S"."situation" column returns '0' in every row instead of the referenced Varchar-Value from the situation table. Since the Query works for the DBMS I cannot see that there is anything I can do to make it work in Base. Therefore this might be a Base issue. Please let me know if you think there is anything I did wrong. Steps to Reproduce: 1. Use PostgreSQL as the DBMS 2. Create the tables and relationships (s. Description-section), add Data 3. Create the Query and run it (s. Description-section) Actual Results: Result-table using Base (see Description-section for Query) [id, hämo, situation] (1, 1, 0) (2, 1, 0) (3, 2, 0) (4, 3, 0) (.......) Expected Results: Result-table directly using PostgreSQL [id, hämo, situation] (1, 1, Körperliche Anstrengung) (2, 1, Tamoxifen plus) (3, 2, spontan) (4, 3, Operation) (.............) Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 143261 [details] query after change I'm using a fresh postgresql 10 install on Ubuntu 18.04 and Libo with postgres:SDBC. Created the three tables and a few records. SELECT "SP"."id", "SP"."hämo", "S"."situation" FROM "situation_p" "SP", "situation" "S" WHERE "SP"."situation" = "S"."id" got just the output you did in Base changed it to: SELECT "SP"."id", "SP"."hämo", "S"."situation" FROM "situation_p" "SP", "situation" "S" WHERE "S"."id" = "SP"."situation" and get what seems to be what you are looking for. (screen shot attached)
(In reply to Drew Jensen from comment #1) > Created attachment 143261 [details] > query after change > > I'm using a fresh postgresql 10 install on Ubuntu 18.04 and Libo with > postgres:SDBC. > > Created the three tables and a few records. > > SELECT "SP"."id", "SP"."hämo", "S"."situation" > FROM "situation_p" "SP", "situation" "S" > WHERE "SP"."situation" = "S"."id" > > got just the output you did in Base > > changed it to: > > SELECT "SP"."id", "SP"."hämo", "S"."situation" > FROM "situation_p" "SP", "situation" "S" > WHERE "S"."id" = "SP"."situation" > > and get what seems to be what you are looking for. (screen shot attached) Hey Drew, while your changes did not fix the problem on my machine (also using Ubuntu 18.04) they certainly helped me to find a solution for the problem. Instead of changing the order of the WHERE-criteria, I changed the order of the FROM-expression: SELECT "SP"."id", "SP"."hämo", "S"."situation" FROM "situation" "S", "situation_p" "SP" WHERE "SP"."situation" = "S"."id" For some magical reason I now get the desired result table. While I think that this order should not be relevant, I have to admit that I was wrong about my evaluation that it can not be fixed in Base itself. I would like to thank you for your very constructive feedback. People like you are awesome.
Ah, thanks. I suppose that means can close this out than as works for me.