first query then use IN in the second query
sorta like this:
<cfset idList = valueList(firstQuery.idField)>
<cfquery name="secondQuery" datasource="blah">
select columns
from table
where table.idField in (#valueList)
</cfquery>
Should head you down towards a quicker solution. The other idea would be
to alias the table against itself and do a join against itself something
like:
<cfquery name="getStuff" datasource="blah">
select t.columns, ta.columns
from table t, table ta
where t.this = ta.this
OR
where t.this != ta.this
</cfquery>
Without knowing the table and the desired result though I am not sure if
this is what your looking for at all.
HTH
Timothy Heald
Web Portfolio Manager
Overseas Security Advisory Council
U.S. Department of State
571.345.2319
The opinions expressed here do not necessarily reflect those of the U.S.
Department of State or any affiliated organization(s). Nor have these
opinions been approved or sanctioned by these organizations. This e-mail is
unclassified based on the definitions in E.O. 12958.
-----Original Message-----
From: brob [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 12, 2003 7:13 PM
To: CF-Talk
Subject: Comparing Values
Hey guys. I have a query that selects about 2,000 rows (by project id) from
a table. Then I use those values to compare with the same table using
another query (but from a different project ID. Any numbers that aren't the
same are appended to a variable named error_list. I'm basically using a
CLOOP query using the first query to loop over the second query. So it's
basically like #firstquery.recordcount# loops and checking each value.
pseudo code
<CFLOOP query="firstquery">
<CFQUERY name="secondquery">
SELECT name
from images
where project_id = #url.id# AND name = #name#
</CFQUERY>
<CFIF secondquey.recordcount EQ 0>
<CFSET error_list = listappened(error_list, name)>
</CFIF>
</CFLOOP>
<CFOUTPUT>#error_list#</CFOUTPUT>
Right now, it takes like minutes to get results! Even if there's only 1
value in the list! Is there a better way to do what I am doing? Or some
way to make it faster? Cos this is slooooooooow! Thankie!
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
