What version of MySQL are you using? If it's a really old one that doesn't support subqueries, check out the MySQL docs. They have a section about rewriting subqueries into JOINs.
cheers, barneyb On Mon, Feb 15, 2010 at 9:16 AM, Barry Mcconaghey <[email protected]> wrote: > > Thanks Barney B. > > ERROR Message: > > Syntax error or access violation: You have an error in your SQL syntax. Check > the manual that corresponds to your MySQL server version for the right syntax > to use near 'select count(*) from articles where zipcode = fddirectory.fdd_z > > Here is the cfquery: > > <cfquery name="Story" datasource="#dsn#"> > SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip, > fddirectory.fdd_state, states.abrev, (select count(*) from articles where > zipcode = fddirectory.fdd_zip) as articleCount > FROM fddirectory, states > WHERE fddirectory.fdd_state = <cfqueryparam value="#statecodeID#" > cfsqltype="CF_SQL_VARCHAR"> > AND fddirectory.fdd_state = states.abrev > ORDER BY fddirectory.fdd_name > </cfquery> > >>Just add: >> >>(select count(*) from articles where zipcode = fddirectory.fdd_zip) as >>articleCount >> >>to the end of your SELECT clause: >> >>SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip, >>fddirectory.fdd_state, states.abrev >>, (select count(*) from articles where zipcode = fddirectory.fdd_zip) >>as articleCount >>FROM fddirectory, states >>WHERE fddirectory.fdd_state = <cfqueryparam value="#statecodeID#" >>cfsqltype="CF_SQL_VARCHAR"> >> AND fddirectory.fdd_state = states.abrev >>ORDER BY fddirectory.fdd_name >> >>That should do it for you, >> >>cheers, >>barneyb >> >> >>> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
