The query executes fine on both CF 4.5 and CF 5, but when I do a CFOUTPUT with viewcart as the query on the box running CF 5 the ProductID column is missing. ProductID exists in all 3 tables.

See comment I posted below.. I changed my db connection type to ODBC as it is on the CF 4.5 box and it now works on the CF 5 box. Apparently based on the db connection type the SQL is returning different results.

Well.. I figured it out.. but please still answer if you know the reason why.

I guess it has to do with SQL and how it works differently in ODBC and OLEDB. The query is a JOIN and I guess it executes differently in ODBC and OLEDB. Does anyone know why? The OLEDB driver I was using is Microsoft.Jet.OLEDB.4.0

I would really like to use OLEDB for various reasons, does anyone know how I would need to rewrite my SQL so that it will work with OLEDB?

Agree with SBennett, use only those fields that used to generate the query. If there is a condition where you want to use both field like ProductID from more than 1 tables, you can rename the fields like this:

You might also want to play around with the dbtype attribute of cfquery. It could be that CF Server is making the connection in a generic way. If you specify a dbtype attribute it ought to modify the way it makes the connection and sends the SQL to the database.