"Sure they've got a point about extra processing for every join but the
convenience far outweighs treating it like a spreadsheet!"
So your saying that
SELECT property_id
FROM property_details
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,%'
will run faster than
SELECT property_details.property_id
FROM property_details, propertyFeaturesToProperties
WHERE propertyFeaturesToProperties.propertyFeatureID IN
(#form.features#)
AND propertyFeaturesToProperties.propertyID =
properties.PropertyID
Hmmm
I beg to differ
Not to mention if you delete, in this case a property feature, you will need
to check each entry in the table just to see if it has that value to remove
it
Not my idea of fun
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Beattie,
Barry
Sent: Wednesday, June 25, 2003 11:43 AM
To: CFAussie Mailing List
Subject: [cfaussie] Re: Cant work out SQL Query error.
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/
---
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/
