I'm currently handling a migration from an Informix system to both mySQL and Postgres (same data in each). Having uploaded the DB's into both systems, from Informix, I am having some problems carrying out a query. The query, which I believe, is in standard SQL is as follows:
SELECT field1, SUM(field2) as Field2, SUM(field3) FROM table1 tb1, table2 tb2 WHERE tb1.field5 BETWEEN '03/07/2006' AND '03/08/2006' AND tb1.field6 = tb2.field7 AND tb1.field8 = tb2.field8 AND tb2.field9 BETWEEN 50.00 AND 150.00 GROUP BY field1 HAVING SUM(field2) BETWEEN 95.00 AND 100.00 ORDER BY 2 DESC, field1; *The 'field(n)' and 'table(n)' values have been substituted for actual field names! The results I get from Informix and mySql return identical results, however postgres includes a great deal more i.e. 11 rows from Informix and mySQL, 203 from postgres. Does anyone have any idea why this disparity may exist? This email may contain information which is privileged or confidential. This information is intended only for the named recipient. If you are not the intended recipient, please be aware that disclosure, copying, distribution or use of this information is prohibited. If you have received this email in error, we would be grateful if you would inform us as soon as possible by telephoning +44 (0) 1769 573431, or by email to [EMAIL PROTECTED] and then delete this email. Views or opinions expressed in this email are those of the writer, and are not necessarily the views of Mole Valley Farmers Limited or its subsidiary companies. Unless specifically stated, this email does not constitute any part of an offer or contract. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
