LEFT OUTER JOIN returns different results on different machines

[jconterio]

I've got a pretty straight forward LEFT OUTER JOIN going, grabbing data from the left side table and getting either matching data from the right or null values. That's the plan anyway, and on my dev machine it works great. But on my live site the query only returns values if the right-hand table matches, not when the values would be null. What's the story here?

SELECT * FROM tblA LEFT OUTER JOIN tblB
ON (tblA.ID=tblB.ID AND tblB.fieldA=#)
WHERE tblA.ID=# AND tblA.fieldB=#
ORDER BY tblA.ID,tblA.fieldC

I'm running Windows XP Prof on my dev box, with Office XP/Access. The live box is running WinNT with Access '97. I tried upgrading the version of Access, but still no dice. Anyone have similar issues in the past? Did I simply fail to upgrade Access correctly, or could I be missing a vital dll somewhere? Is this a known problem? Any ideas would be a big help. Thanks!

 

[tlbroadbent]

I beleive Access 97 doesn't process the query properly because the syntax is non-standard, at least for Access 97. Try the following modified query to see if it will return correct results on both machines.

SELECT * FROM tblA
LEFT OUTER JOIN
(SELECT * FROM tblB WHERE tblB.fieldA=#) As qryB
ON (tblA.ID=qryB.ID)
WHERE tblA.ID=# AND tblA.fieldB=#
ORDER BY tblA.ID, tblA.fieldC If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[jconterio]

That did the trick. Can't thank you enough! Yeah, I guess my ON clause may have been a bit non-standard, though it certainly gave me what I wanted (when I moved the 'tblB=#' statement into the WHERE clause it basically acted as an inner join instead). I guess Access can be exciting that way. Thanks again.