Hi Trevor, I think that query might work as-is in MySQL 4.1. However, the current production version doesn't support subqueries. Your query can be rewritten with a join like this I think:
SELECT C.*, COUNT(*) AS LINK_COUNT FROM Categories C INNER JOIN Links L ON (L.CAT_ID=C.CAT_ID AND L.LINK_APPROVED='Yes') GROUP BY C.CAT_ID ORDER BY CAT_NAME Hope that helps. Matt ----- Original Message ----- From: "Trevor Sather" Sent: Thursday, October 23, 2003 6:49 PM Subject: Syntax for SQL Query - used to work with Access > Hi > > This query used to work with an Access database: > > SELECT *, (SELECT COUNT (*) FROM Links WHERE Links.CAT_ID = > Categories.CAT_ID AND LINK_APPROVED = 'Yes') AS LINK_COUNT FROM > Categories ORDER BY CAT_NAME ASC > > Now that we've upgraded to MySQL, the same ASP script returns this > error: > > ADODB.Recordset.1 error '80004005' > > > SQLState: 42000 > Native Error Code: 1064 > [TCX][MyODBC]syntax error near 'SELECT COUNT (*) FROM Links WHERE > Links.CAT_ID = Categories.CAT_ID AND LINK_AP' at line 1 > > Could anyone tell me what I need to do to fix it? > > Unsubscribing now so very grateful if you could e-mail me with any > advice -- thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
