Hi Stephen
SQL Server 7 or 2000 should allow you to run this query...
<cfquery name="GetChildElements" datasource="#Request.cfa.objectstore.dsn#"
dbtype="ODBC">
SELECT sm.chardata AS SiteModel,
sm.objectid AS ObjectID,
ix.integerdata AS L3Index
FROM properties sm, properties ix
WHERE sm.objectid = ix.objectid
AND sm.propertyname = 'sitemodel'
AND sm.chardata LIKE '#SearchTitle#%'
AND PATINDEX('%,%,%,%', sm.chardata) = 0
AND ix.integerdata = 1
ORDER BY sm.chardata ASC
</cfquery>
Note, that you have to ensure that your integerdata is unique for each
property entry for an objectid, otherwise you will get duplicates in a join
like this (this situation shouldn't arise, unless you were searching on
integerdata = 0 for instance).
Regards,
Peter Farland
Software Engineer
Macromedia
+617 219 2207
-----Original Message-----
From: Stephen Collins [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 6:11 PM
To: Spectra-Talk
Subject: SQL Gurus - HELP!!!
Anyone help me with this one? I'm trying to get a very restricted set of
object data based on object attributes stored in the Spectra properties
table.
SQL Query:
<cfquery name="GetChildElements" datasource="#Request.cfa.objectstore.dsn#"
dbtype="ODBC">
SELECT sm.chardata AS SiteModel,
sm.objectid AS ObjectID,
ix.integerdata AS L3Index
FROM properties AS sm, properties AS ix
WHERE sm.objectid=ix.objectid
AND sm.propertyname='sitemodel'
AND sm.chardata LIKE '#SearchTitle#%'
AND ix.integerdata=1
ORDER BY sm.chardata ASC
</cfquery>
All this works, except that I am getting back too much data which I then
have to filter with CF code (unfortunately, too slow) by looping over the
query and excluding items with inappropriate data.
What I want to add to the query is a restriction that the return for
"sm.chardata AS SiteModel" can be a comma-separated list (as it already is),
but of no more then two items' length, e.g. I would accept "Quarantine,About
AQIS" but not "Food,Meat,Pork". The SiteModel attribute is ALWAYS a comma
separated list of 1-4 items' length. Currently, the query gets me back a
squillion hits which then have to be filtered down. The whole process takes
about 30 seconds (which is about 27 seconds too long).
Does SQL support this? Is there a better way?
Steve
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
------------------------------------------------------------------------------
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/spectra_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
