Yeah agreed! I get students really shying away pulling records that are made up of 3 tables or more. dunno why. They think they're really cleaver using string processing to manip the records instead of using the inbuilt strengths of all (decent) databases.
Sure they've got a point about extra processing for every join but the convenience far outweighs treating it like a spreadsheet! just my 2c barry.b -----Original Message----- From: Steve Onnis [mailto:[EMAIL PROTECTED] Sent: Wednesday, 25 June 2003 11:35 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Cant work out SQL Query error. I am sorry to say but thats what you get for using lists in databases to hold references to other tables primary keys. In my optinion, if you are going to have a database that requires to store relationship information, then lookup tables should be used. Want to know why? Just look at this thread Messy messy Much easier SELECT property_details.property_id FROM property_details, propertyFeaturesToProperties WHERE propertyFeaturesToProperties.propertyFeatureID IN (#form.features#) AND propertyFeaturesToProperties.propertyID = properties.PropertyID This way is not only easier to code, it's more efficient and much easier to maintain your database Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Pragnesh Sent: Wednesday, June 25, 2003 11:22 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Cant work out SQL Query error. True but property_features is a text field as it can have several features. 1 record can have multiple features. "Taco Fleur" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I am assuming your form.features is a list of IDs ? SELECT property_id from property_details WHERE (pkBuildingID IN (1, 2, 3)) Code would be SELECT property_id from property_details WHERE (pkBuildingID IN (#form.features#)) Is a lot easier and works many times faster.. -----Original Message----- From: Pragnesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, 25 June 2003 11:15 AM To: CFAussie Mailing List Subject: [cfaussie] Re: Cant work out SQL Query error. Still doesnt work. Am generating the where clause dynamically featurestr=""; for (i = 1; i LTE listlen(form.features); i = i+1) { fid=ListGetAt(form.features,i,","); featurestr=featurestr & "property_features = '" & fid & "'"; featurestr=featurestr & " OR property_features LIKE '%," & fid & "'"; featurestr=featurestr & " OR property_features LIKE '" & fid & ",%'"; featurestr=featurestr & " OR property_features LIKE '%," & fid & ",%'"; Am getting this as the output: property_features = '1' OR property_features LIKE '%,1' OR property_features LIKE '1,%' OR property_features LIKE '%,1,%' OR property_features = '2' OR property_features LIKE '%,2' OR property_features LIKE '2,%' OR property_features LIKE '%,2,%' When I use it in a query, it throws an error <cfquery name="getsearchresult1" datasource="#config.datasource#"> SELECT property_id from property_details where #featurestr# </cfquery> <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Single quotes instead of double, but also: Change OR property_features LIKE "2" To: OR property_features = '2' Steve c -----Original Message----- From: Pragnesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, 25 June 2003 10:52 AM To: CFAussie Mailing List Subject: [cfaussie] Cant work out SQL Query error. HI All, Cannot work out why the sql query is throwing an error. Worst thing is when I execute the query in MS ACCESS it runs but thro CF it throws this error. Any suggestions would be appreciated. Error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 8. Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly. SQL Query is: SELECT property_id from property_details where property_features LIKE "1" OR property_features LIKE "%,1" OR property_features LIKE "1,%" OR property_features LIKE "%,1,%" OR property_features LIKE "2" OR property_features LIKE "%,2" OR property_features LIKE "2,%" OR property_features LIKE "%,2,%" --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ ---------------------------------------------------------------------------- This email, together with any attachments, is intended for the named recipient(s) only and may contain privileged and confidential information. If received in error, please inform the sender as quickly as possible and delete this email and any copies from your computer system network. If not an intended recipient of this email, you must not copy, distribute or rely upon it and any form of disclosure, modification, distribution and/or publication of this email is prohibited. Unless stated otherwise, this email represents only the views of the sender and not the views of the Queensland Government. ---------------------------------------------------------------------------- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
