Re: MSSQL to MySQL

[Bill Karwin]

MySQL 4.1 and later versions support subqueries.
You can get your MySQL version with: "SHOW VARIABLES LIKE 'version';"
If it's 4.0 or less, you can't use subqueries.

However, you should be able to rewrite this query without subqueries, and it may even run faster this way:

SELECT o.*,
i.ItmName, i.CategoryID, i.ClientID,
c.CatName AS CategoryName,
CONCAT(cl.FName, ' ', cl.LName) AS ClientName,
it.ItmName AS ItemTYpeName,
it.RecordID AS ItemTypeID
FROM Offers AS o
INNER JOIN Items AS i ON i.RecordID = o.ItemID
INNER JOIN Categories AS c ON c.RecordID = i.CategoryID
INNER JOIN Clients AS cl ON cl.RecordID = i.ClientID
INNER JOIN ItemTypes AS it ON it.RecordID = i.ItmType
ORDER BY o.DteAdded DESC

Note also the use of CONCAT() instead of the Microsoft-proprietary '+' syntax for string concatentation.

Regards,
Bill K.