SQL selecting from multiple tables

[libreofficeguy]

Hi guys I am working with the following query. Now this query works fine, I use it for search. however I want to add a column from a different table.I know that adding a separate form and using that for the other table would solve this problem. But I am wondering if it’s possible to select from 2 different tables in this way. So in this case, food name would be selected from table tbl_CMFD and food ID from a different table. Possible?

SELECT
*
FROM

   "tbl_CMFD"

WHERE

 ((UPPER("Food Name")    LIKE '%' || UPPER (:Var_FN) || '%' ) OR (:Var_FN  IS NULL))

AND

((UPPER(“Food ID”) LIKE ‘%’ || UPPER (:Var_FID) || ‘%’ ) OR (:Var_FID IS NULL))

[Ratslinger]

Hello,

You can select data from many, many different tables in an SQL statement.

The statement you present doesn’t give any kind of picture as to the selection material or reasoning of what is wanted. It makes no sense to use ‘UPPER’ & ‘LIKE’ when dealing with an ID (probably INTEGER field).

Also, if you have 3 records in each of the tables, run this SQL (of course with the added table reference in the FROM section) and leave both of the parameters NULL you will end up with 9 records in the result. Each record in Table 2 will be attached to each record in Table 1: 3 X 3 = 9!

What is needed is more clarification as to what you are doing. A sample .odb file will also help and which database you are using.

Edit 2019-01-10:

Based upon your question and follow up ( and lack of), it appears you have very little knowledge of SQL. SQL is the language used to communicate to a database. It is typically based upon standards. Depending upon the database and its’ version it will most likely follow a different standard. HSQLDB v1.8 embedded (LO Base default) is very old and follows an older standard. Firebird 3.0 embedded ( LO Base default with ‘Experimental features’ enabled) follows a newer standard and thus has different capabilities and in some cases different syntax to accomplish the same things. Other databases, and again dependent upon their version, may have even different standards. And this doesn’t even cover what non standard functions may be in a database. I hope this enlightens you as to the need for specific information.

As for the multiple tables, in taking data from a second (or other) table in a query, this second table should have some connection to the first or primary table otherwise it makes little or no sense to obtaining it. This “connection” is certainly missing from your description (what little there is). In SQL, this connection would be part of the WHERE condition.

[libreofficeguy]

Hi, thank you for replaying, I appreciate it.

I am trying to make a wildcard filter based on the following tutorial:

It’s just that in this example both the columns he is using are from the same table. I have a columns in a different table that I want to add to my search filter.

cheers

[Ratslinger]

Please only use answers to actually answer the original question. Instead use add a comment or if needed, edit original question with this additional information.

You have not provided details, as asked for, to assist in the matter. Some unrelated tutorial is not going to be of help. What is necessary is what is specific to your situation.