Can I display data from two unrelated tables using SQL?

English
#1

I have set up a simple case to test this:

Form ‘main’, data coming from table ‘filter’
field ‘name’ from table ‘filter’
Subform ‘customers’, data coming from table ‘customers’
table control showing data from an SQL command
SQL command:
select name from customers c , filter f where c.name like f.name || “%”
or f.name is null

When I run this in the SQL query window (from the form control SQL command window) I get the error ‘table not found in statement …’

Is it not possible to pull data from two unrelated tables? (field and table names names are spelled correctly)

#2

You didn’t quotate the names of tables. So it could be there are upper cased or lower cased characters.

If you link two tables in a query the shown data will be all rows of “customers” multiplicated with all rows of “filter”. This would work if there is only one row in table “filter”.

#3

Thank you RobertG for your reply.

If I knew how to attach the sample database I would attach it.

If all names are spelled correctly including case, my understanding was that the SQL command I posted would be equivalent to an inner join which is what I want:

All records from ‘customers’ that have a name that matches the name in ‘filter’. But the query won’t let me enter that SQL statement. It complains about a ‘missing table’ which I don’t understand.

The command works without the ‘where’ part, which gives me all customers.

#4

Correction: It only works with the SQL command without the ‘where’ part (select name from customers) if I enter the full name in the filter table which gives me that one customer.
In the Data part of the forms properties the link fields are set to customer.name and filter.name.

#5

Switch to the button for upload files (7. button from the left)

#6

Sorry if I seem to be stupid but own the main screen under your reply there are only 4 buttons:
share, bookmark, suggest solution and watching.

Below that there are a number of entries under the heading ‘Suggested topics’. And the last line reads 'There is 1 unread and 25 new topics …

If I hit ‘reply’ to reply to your post I get the window where I can type my response and at the bottom of that window there are 2 buttons: Reply and cancel.

I can’t find the upload button.

#7

See the editor, where you have written all this: 13 Buttons will appear, one button with arrow up will “Upload” the file.

#8

The actual type of database you are using is the info that would be even more important than LibreOffice version and operating system. Base is not a database. It is a tool to work with databases. You can read the type of database from the status bar of your Base window.
Most Base documents are connected to some spreadsheet file to enable mail merge or label printing in Writer. Such a connection to a pseudo-database does not support any relations or cross-table queries.

#9

Thank you for your patience. I am indeed stupid.

Here is the database.

FilterKunden.odb (12.6 KB)

#10

Thanks Villeroy.

I am using a HSQLDB if that answers your comment? Meanwhile I have uploaded the sample database.

#11

Some things I see when testing: “%” is double quoted. Put it in ‘%’.

This code will work with the example:

select "Name" 
from "Kunden" "c" , "FilterKunden" "f" 
where "c"."Name" like "f"."Name" || '%'
or "f"."Name" is null
#12

Here is the test on my end:

Screenshot 2022-05-25 at 12.10.25
Screenshot 2022-05-25 at 12.10.252806×1492 234 KB

#13

Beginning with “k” all double quotes are lost. Also “Filterkunden” instead of “FilterKunden” in the example database. But main problem seem to be nuill instead of null at the end of the code.
FilterKunden.odb (12.6 KB)

#14

Thank you for your patience. With all the trees around I don’t even see the wood. :slight_smile:

Syntactically it is clean now. However, if I enter a partial name (e.g. ‘Sp’ instead of ‘Specht’) I don’t get a result. If I enter the full name that one customer entry is shown.

The where clause should find all records starting with ‘Sp’ in the example above, shouldn’t it?

#15

It is the problem of your form construction. You have linked “Name” to “Name”.

Try it with forms, which aren’t connected (no subforms)
FilterKunden.odb (13.1 KB).

Remember: Base couldn’t be used for input data in such a table. You have used two tables, both with alias and both without primary key in the shown fields. Try the two queries of the example…

By the way: Seems you are German. Why didn’t you post it in https://ask.libreoffice.org/c/german/6?

#17

So that was the trick. Thank you very much. I have learned a valuable lesson.

Why do I post here? It’s a habit, to be honest. Over the years I have found English communities more helpful. Are you on the German forum, too?

It would be easier for me to communicate in German, though, because my English is not the best in the world. :slight_smile:

Once more: Thank you very much for your help and patience.

#16

Basic recipe for a filter form:

  1. Filtering main form: SELECT * FROM “Criteria” WHERE “ID” = 0 selects only one distinct row from the criteria table.
  2. Form properties: Disallow everything except modification of data. This way the user can not tab into anyrecord other than the specified one. And you do not need a navigation bar while editing this single record.
  3. You can re-use the same criteria table for other forms if your primary key is some integer (not auto-integer).
  4. Based on Robert’s suggestion, you get case-insensitive matches with the help of function Lower():
SELECT "Name", "Vorname", "Ort" 
FROM "Kunden" "c", "FilterKunden" "f" 
WHERE Lower("c"."Name") LIKE Lower("f"."Name") || '%' OR "f"."Name" IS NULL

Alternatively, you could switch the column types to VARCHAR_IGNORECASE.
This 2-table row set is not editable. If you want an editable filtered row set, create a subform based on a parameter query including a single table with its primary key (simply select all columns):

SELECT * FROM "Kunden"
WHERE Lower("Name") LIKE Lower(:BeginsWith) || '%' OR :BeginsWith IS NULL

:BeginsWith is a named parameter. Link the subform’s slave field to BeginsWith (without leading colon) and the master field to the corresponding column of the filtering parent form.

#18

Thank you Villeroy. I will try your solution, too. I’ll see whether I can get it to work and will report back. :slight_smile:

#19

A document tells more than 1000 words.
FilterKunden(1).odb (13.7 KB)
Have a look at the modified filter table, the queries and the form.

#20

Thank you Villeroy. Interesting solution which I need to digest in detail, though. But I get an idea! Thank you again for your time.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.