Hi,

I have two tables:

- Customers
- Invoices

I got an *sql logic error* trying to execute following query using Zeos Component (ZQuery):

"SELECT Invoices.*, Customers.Name
FROM Invoices
LEFT JOIN Customers
ON Invoices.CustomerID= Customers.ID
* WHERE Customers.Name = 'Tom' "*

excluding the Where clause make the query run without problems.

Thank you
Max
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20100112/c0d0d73e/attachment.html>

Hello Lazarus-List,

Tuesday, January 12, 2010, 6:07:37 PM, you wrote:

MF> I got an *sql logic error* trying to execute following query using Zeos MF> Component (ZQuery):
MF> "SELECT Invoices.*, Customers.Name
MF> FROM Invoices
MF> LEFT JOIN Customers
MF> ON Invoices.CustomerID= Customers.ID
MF> * WHERE Customers.Name = 'Tom' "*
MF> excluding the Where clause make the query run without problems. From my point of view it is a "SQL logic error", I think it should be: "SELECT Invoices.*, Customers.Name
FROM Customers
RIGHT JOIN Invoices
ON Invoices.CustomerID= Customers.ID
WHERE Customers.Name = 'Tom' "

Note the swapping of Customers and Invoices, the other way the SQL engine will sequentially scan all invoices to join them with customer and perform the "where". Anyway I do not know why ZQuery raises and exception :-? (I'm not using Zeos at all).

Thank you JoshyFun!

The problem remain. Now i can't apply any filter to Invoices fields! :(

For example this query raise an exception:

"SELECT Invoices.*, Customers.Name
FROM Customers
RIGHT JOIN Invoices
ON Invoices.CustomerID= Customers.ID
* WHERE Invoices.Number > 3"*

2010/1/12 JoshyFun

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20100112/93938711/attachment.html>

On Tue, 12 Jan 2010 19:01:48 +0100, Massimo Fazzolari wrote about Re: [Lazarus] Zeos Component Join Query:

The join should be an INNER JOIN if you want constraints on columns of both tables..

What has any of this to do with Lazarus?

Massimo, please before trying with Lazarus/Zeos, please test your query using an external database administration tool like FlameRobin or IbExpert of you are using Firebird.

99% of the time, the problem isn't Zeos, but the query itself.

Thank you for reply Leonardo.

I'm using SqLite. I tested the query with sqlite command line tool. The query is correct. The problem is Zeos.

2010/1/12 Leonardo M.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20100113/ac5d9b38/attachment.html>

Massimo Fazzolari wrote

Thank you for reply Leonardo. I'm using SqLite. I tested the query with sqlite command line tool. The query is correct. The problem is Zeos.

I'm using ZEOS for years, and it works correct (postgresql). Such query (as mentioned here) work without problems.

zeljko

Hello Lazarus-List,

Wednesday, January 13, 2010, 11:26:19 AM, you wrote:

MF> I'm using SqLite. I tested the query with sqlite command line tool. The MF> query is correct. The problem is Zeos.

I had seen some references to that problem with Zeos and SQLite, but not with other DB engines. So it looks like a Zeos+SQLite bug so it should be reported at http://zeosbugs.firmos.at/

Ok. Thanks JoshyFun. I will report the bug to http://zeosbugs.firmos.at/

2010/1/13 JoshyFun

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20100113/a7727d40/attachment.html>