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')