I have updated my query to run correctly against my database, but when I run it in coldfusion it crashes.
The error is: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. Here is the query: <cfquery datasource="#Request.MainDSN#" name="vacant_unit"> SELECT HOAs.hoaID, owners.vacent, owners.project, owners.ownerID AS owners_ownerID, ownerUnits.ownerid AS ownerUnits_ownerid ownerUnits.smokingrestrictions, ownerUnits.rent, ownerUnits.squarefootage FROM (HOAs INNER JOIN owners ON HOAs.hoaID = owners.project) INNER JOIN ownerUnits ON owners.ownerID = ownerUnits.ownerid WHERE (((HOAs.hoaID)=<cfqueryparam value="#hoa#">) AND ((owners.vacent)= TRUE) AND ((owners.project)=<cfqueryparam value="#hoa#">)) </cfquery> It tells me that the error is in the WHERE CLAUSE. Anyone notice a problem with my query? -----Original Message----- From: Robert Makowski [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 11:38 AM To: CF-Newbie Subject: sql questions I am trying to write an sql query to get the vacant units from one property. When I run the query, I get all owners that have vacant units, disregarding the particular property that the owner's unit resides. Here is the code that I have so far. <cfset hoa="#form.HOA#"> <!---<cfquery datasource="#Request.MainDSN#" name="vacantunits"> SELECT * FROM HOAs INNER JOIN owners ON HOAs.hoaID = owners.project WHERE HOAS.NAME = <cfqueryparam value="#hoa#"> AND owners.vacent = true </cfquery> ---> <cfquery datasource="#Request.MainDSN#" name="unit"> SELECT * FROM OWNERUNITS INNER JOIN OWNERS ON OWNERUNITS.OWNERID = OWNERS.OWNERID, HOAS WHERE HOAS.HOAID = OWNERS.PROJECT AND OWNERS.VACENT = TRUE </cfquery> The first query is a reuse from another page that uses a similar query. The bottom query is the one that I'm trying to get to work. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2582 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
