I'm not clear what you're trying to accomplish, but you can use what are
called subqueries in your WHERE clause, e.g.
SELECT ID,SEQUENCE,TEXT
FROM MyOtherSource
WHERE ID IN
(SELECT ID
FROM MySource
WHERE NAME LIKE '#FORM.junk1#%')
The other thing you can do is use the VALUELIST function, e.g.,
SELECT ID,SEQUENCE,TEXT
FROM MyOtherSource
WHERE ID IN (#valueList(<firstQueryName>.ID)#)
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 5:50 PM
To: CF-Talk
Subject: Using a SQL query to drive another SQL query
I'm running into a bit of a problem making my code work; any suggestions?
I'm trying to get data from one SQL routine, and then feed that data into
another.
Here is the first one:
SELECT ID, NAME
FROM MySource
WHERE NAME LIKE '#FORM.junk1#%'
I'm then loading the ID values into an array, and trying to feed that array
into
another SQL query, without any success.
<CFSET Sequence = "1">
<CFSET queryArray = ArrayNew(1)>
<CFLOOP query ="GetList">
<CFSET myArray[#Sequence#] = #ID#>
<CFSET Sequence = IncrementValue(Sequence)>
</cfloop>
SELECT ID,SEQUENCE,TEXT
FROM MyOtherSource
WHERE ID = '#Current#'
<CFLOOP INDEX="Counter"
FROM = "1" TO = "#getList.RecordCount#">
<CFSET Current = #getList[Counter]#>
Unfortunately, I get
Error resolving parameter CURRENT
which leads me to believe that CF is trying to use the CURRENT variable
before
the CFLOOP happens.
I apologize if I'm making a stupid mistake here; I'm still fairly new at CF,
and
tackling a few projects that I'm willing to admit are over my head (I was
teased
by a friend last week--"Your program has nested CFLOOPs, and you're using
CFQUERY and SQL to go get the data you want, but you don't know how to use a
<BR> tag...??). Any advice would be appreciated.
Allan Davis
[EMAIL PROTECTED]
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists