Select Count

[manking]

Any Ideas whats wrong with this?

<CFQUERY name=&quot;gLis&quot; datasource=&quot;#DSName#&quot;>
SELECT DISTINCT Types.ID, Types.Type,
(Select Count(*)
From Listings
Where TypeID = types.ID AND Listings.Approved = 1) AS TotalListings
FROM Types INNER JOIN Listings ON Types.ID = Listings.TypeID
ORDER BY Types.Type
</CFQUERY>


Error Message
ODBC Error Code = 42000 (Syntax error or access violation)


[TCX][MyODBC]You have an error in your SQL syntax near 'Select Count(*) From Listings Where TypeID = types.ID AND Listings.Approved' at line 2

 

[swampBoogie]

Mysql does not support subqueries.

You have to rewrite with a (outer) join and group by