Selecting a specific record from within...

I have been using a certain set of code to query a database
for a recordset:





%26lt;CFQUERY NAME=''RandPick'' DATASOURCE=''MyDB''%26gt;

SELECT PickID

FROM MyTable

%26lt;/cfquery%26gt;





Then I randomly select 1 number within that record count:





%26lt;CFSET RND_PICK = RandRange(1, #RandPick.RecordCount#)%26gt;





Finally, I use STARTROW to display a field from the randomly
selected record (which I pass to a flash file):





MyFile.swf?PickID=%26lt;CFOUTPUT QUERY=''RandPick''
StartRow=''#RND_PICK#'' MAXROWS=''1''%26gt;#PickID#%26lt;/CFOUTPUT%26gt;





The final ouput might look something like this:





MyFile.swf?PickID=46





This all works fine, but now I want to do something a bit
more complicated: I want to take that field value that I've found
(in this case '46') and I want to query a second table that
contains detailed information about PickID 46:



%26lt;CFQUERY NAME=''DetailedPickInfo'' DATASOURCE=''MyDB''%26gt;

SELECT PickID, PickDescription

FROM MySecondTable

Where PickID = '46'

%26lt;/cfquery%26gt;





My problem is I can't use my STARTROW trick to embed the
selected PickID in my second query:



%26lt;CFQUERY NAME=''DetailedPickInfo'' DATASOURCE=''MyDB''%26gt;

SELECT PickID, PickDescription

FROM MySecondTable

Where PickID = %26lt;CFOUTPUT QUERY=''RandPick''
StartRow=''#RND_PICK#'' MAXROWS=''1''%26gt;'#PickID#'%26lt;/CFOUTPUT%26gt;

%26lt;/cfquery%26gt;



Obviously that is not allowed.



Could some smart person please help me figure out a way to do
this?





Basically I am trying to select a random record from within a
query, and then JOIN the selected record with a second table (from
which I want to grab a piece of data).



I understand that my STARTROW method is a bit of a hack, and
there is probably a better way to do this whole thing.



Any help would be appreciated.







On a related note, I would also like to know a general
solution for selecting (for example) the 5th record from a query
(or the 32nd record from a query)



ThankyouSelecting a specific record from within...
you are using a wrong var. your randomly selected PickID is
stored in

RND_PICK variable.



so your query:



%26gt; %26lt;CFQUERY NAME=''DetailedPickInfo''
DATASOURCE=''MyDB''%26gt;

%26gt; SELECT PickID, PickDescription

%26gt; FROM MySecondTable

%26gt; Where PickID = %26lt;CFOUTPUT QUERY=''RandPick''
StartRow=''#RND_PICK#''

%26gt; MAXROWS=''1''%26gt;'#PickID#'%26lt;/CFOUTPUT%26gt;

%26gt; %26lt;/cfquery%26gt;



should simply be



%26lt;CFQUERY NAME=''DetailedPickInfo'' DATASOURCE=''MyDB''%26gt;

SELECT PickID, PickDescription

FROM MySecondTable

Where PickID = '#RND_PICK#'

%26lt;/cfquery%26gt;



(if PickID field is an INTEGER field - do not use single
quoted around

#RND_PICK#)



%26gt; On a related note, I would also like to know a general
solution for selecting

%26gt; (for example) the 5th record from a query (or the 32nd
record from a query)



array notation. #yourquery.yourcolumnname[rownumber]#



--



Azadi Saryev

Sabai-dee.com


http://www.sabai-dee.com

Selecting a specific record from within...
Instead of doing this:



MyFile.swf?PickID=%26lt;CFOUTPUT QUERY=''RandPick''
StartRow=''#RND_PICK#'' MAXROWS=''1''%26gt;#PickID#%26lt;/CFOUTPUT%26gt;



perhaps you should set a variable with the value returned
from your cfoutput statement, then use that variable as both your
URL parameter and picID value in your second query. Something like
this: %26lt;CFSET Pic_ID = #RandPick.PickID[RND_PICK]#%26gt;



(Note to Azadi: the value of RND_PICK is not the value of
PickID, but the value of the record count of the row returned by
the first query that contains the PickID of interest.)



Phil
That worked perfectly - thankyou very much!
paross1 wrote:



%26gt; (Note to Azadi: the value of RND_PICK is not the value
of PickID, but the

%26gt; value of the record count of the row returned by the
first query that contains

%26gt; the PickID of interest.)



you are absolutely right, phil! my eyes must be giving up on
me...

--



Azadi Saryev

Sabai-dee.com


http://www.sabai-dee.com