In your working query you are asking firebird to take the rows from comm that match the where clause and join that small subset of data with the matching rows in tran. In the one that never returns you are asking firebird to join every table in tran with every row in comm and then apply the filter. The reason the one user works and the other doesn't is related to the number of rows in their tables.

An index on commissions.invoicenumber will speed up the working query even more


Sent via my BlackBerry from Vodacom - let your email find you!

-----Original Message-----
From: "brian.matchey" <brian.matchey@...>
Sender: firebird-support@yahoogroups.com
Date: Fri, 06 Jul 2012 14:44:30
To: <firebird-support@yahoogroups.com>
Reply-To: firebird-support@yahoogroups.com
Subject: [firebird-support] one query works, the other never comes back

Hi,

I have a problem where a select query works on one user's data but not another's. (Firebird server 2.5.1.26351)
Strangely, if I reverse the join and from statements, the query works for both � why?

This query fails for one user: (by fail, I mean it never returns)
Select
TRANSACTIONS.INVOICENUMBER,
TRANSACTIONS.ITEMNUMBER
From TRANSACTIONS
LEFT JOIN COMMISSIONS ON (COMMISSIONS.INVOICENUMBER=TRANSACTIONS.INVOICENUMBER) AND (COMMISSIONS.ITEMNUMBER=TRANSACTIONS.ITEMNUMBER)
WHERE (Commissions.Status IS NOT NULL)
AND (Commissions.InvoiceNumber = '-2')

This one works for that same user:
Select
TRANSACTIONS.INVOICENUMBER,
TRANSACTIONS.ITEMNUMBER
From COMMISSIONS
LEFT JOIN TRANSACTIONS ON (TRANSACTIONS.INVOICENUMBER=COMMISSIONS.INVOICENUMBER) AND (TRANSACTIONS.ITEMNUMBER=COMMISSIONS.ITEMNUMBER)
WHERE (Commissions.Status IS NOT NULL)
AND (Commissions.InvoiceNumber = '-2')





[Non-text portions of this message have been removed]