The following SQL query on two tables linked with foreign key "extract":

Code: Select all

SELECT dna_code.code FROM dna_code JOIN diversity2016 ON diversity2016.extract=dna_code.extract

is considered incorrect by Kexi, the check function return:

Code: Select all

The query is incorrect
"JOIN" is a reserved keyword

I'm not really used to SQL, so maybe I'm doing a basic SQLite mistake but... I don't get it, it seems simple.

I know I would get the same result with

Code: Select all

SELECT dna_code.code FROM dna_code, diversity2016 WHERE dna_code.extract = diversity2016.extract

but I will need to use LEFT JOIN later, so I'd like to understand what is wrong.

Hi clemkad,
I don't know version of Kexi you use but Kexi 2 and 3.0 just does not recognize JOINs. Users have to use WHERE for now.

The (inner) join "SELECT dna_code.code FROM dna_code JOIN diversity2016 ON diversity2016.extract=dna_code.extract" can be replaced by "SELECT dna_code.code FROM dna_code WHERE diversity2016.extract=dna_code.extract".

Bottom line. What is recognized by SQLite (or other database engine that is used) does not imply features because Kexi processes the SQL on its own (thus gaining uniformity not typically present in SQL solutions). This also gives some extra security and extra features. SQL "standard" does not even define what LENGTH() means. Kexi's SQL does.
The cost is: at Kexi we have more development work. Example extras: https://community.kde.org/Kexi/Plugins/ ... _Functions

Hi, Jaroslaw,

Please clarify your answer with a little more detail. I am not sure that I understand the issues fully. Since I am struggling to implement an sql query that runs under psql into one that kexi will accept, I would like to know what kexi does and does not accept. (There is no list given in the Manual - but I would add it in for you if you have an ad-hoc list).

We have Postgresql, and like other sql database products this provides support for the ansi sql specification. JOIN is part of the ansi spec* and to my limited knowledge all major sql implementations support this. I thought kexi would pass sql queries written in the sql editor window unedited, but now realise this is not so. I don't understand why this is so for an ansi statement when I thought ansi was the industry one-size-fits-all anyway.

I understand what you say, in principle, but did not realise that the ramifications of the necessary approach went so deep. Sorry, I am not meaning to be critical, just want to understand the how and why and be able to use sql in kexi more efficiently.

best
Ian

*well, it is both in my ansi app and my postgresql app on my tablet, at least and the number of queries re same on the internet could fill a laptop hard drive.

The principle is as follows:
- if you enter SQL into Kexi's SQL view you need to use Kexi's SQL dialect. This is because queries are sent to Kexi first, not postgres, and Kexi needs to understand then. With it joins can currently be expressed only using the WHERE keyword, i.e. all the joins are the old-style inner joins.
- if you run any tool external to Kexi, like pgAdmin or command line or a script or program, you can take advantage of whatever PostgreSQL supports. This is because queries as sent directly to the postgresql server.

In case if I did not share this, simulating LEFT JOIN using WHERE looks as follows: use UNION and add records not having joins.

Online example - both queries return 213 records:

1. Modern LEFT JOIN: see https://www.w3schools.com/sql/trysql.as ... _join_left

2. OLD school simulated: visit https://www.w3schools.com/sql/trysql.as ... _join_left and paste this:

Code: Select all

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID

UNION

SELECT CustomerID, CustomerName, NULL FROM Customers
WHERE Customers.CustomerID NOT IN (SELECT CustomerID FROM Orders)

ORDER BY CustomerName;


I do realize the approach is awful for readability and effort. At one point such tricks will not be necessary in Kexi at all.