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" <[email protected]> Sender: [email protected] Date: Fri, 06 Jul 2012 14:44:30 To: <[email protected]> Reply-To: [email protected] 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
