Skip to main content
R
RonFusion
Known Participant
August 5, 2006
Question

cfquery and ListFind()

  • August 5, 2006
  • 2 replies
  • 791 views
I have a query 'qryTest1' that queries question and answer values from a table. the values displayed are

ex: Q1 A1
Q2 A2
Q3 A3
Q1 A4
Q1 A5

Now, I have other query 'qryTest2' that queries another table to find answers that are valid for Q1

What i need to do is to display from qryTest1 is only those answers that are valid for Q1

What i m doing is

<cfoutput query = qryTest1>
<cfif question_number EQ Q1 AND ListFind(qryTest2.answer_values, answers)
display the question and answers
<cfelse>
do not display that record
</cfif>

What happens is that it displays the question and answer value for only first Q1, but the remaining two Q1's , even if they are valid, it doesn't display the values.

What is missing here

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
August 6, 2006
Does qryTest2 produce a number of records like this:
A1
A3
etc
or a list
A1,A3, etc

If the former, you need to use the ValueList function to convert it to a list.
R
RonFusionAuthor
Known Participant
August 6, 2006
quote:
Originally posted by: Dan Bracuk
Does qryTest2 produce a number of records like this:
A1
A3
etc
or a list
A1,A3, etc

If the former, you need to use the ValueList function to convert it to a list.




Yes, its the former one and it wirtes the individual records to the file. So basically when it writes the records, i check answer values are valid, then write else do not. Should I still do the valuelist
R
RonFusionAuthor
Known Participant
August 6, 2006
quote:
Originally posted by: RonFusion
quote:
Originally posted by: Dan Bracuk
Does qryTest2 produce a number of records like this:
A1
A3
etc
or a list
A1,A3, etc

If the former, you need to use the ValueList function to convert it to a list.




Yes, its the former one and it wirtes the individual records to the file. So basically when it writes the records, i check answer values are valid, then write else do not. Should I still do the valuelist


Sorry, after reading again, I am already doing a ListFind(), y do I need valueList for qryTest2, I would probably need something like that for may be qryTest1
M
MaryJo
Inspiring
August 6, 2006
Why not just use a query-of-query instead?

<cfquery name="questions" dbtype="query">
SELECT * FROM qryTest1
WHERE question_number = 'Q1'
</cfquery>

R
RonFusionAuthor
Known Participant
August 6, 2006
quote:
Originally posted by: MaryJo
Why not just use a query-of-query instead?

<cfquery name="questions" dbtype="query">
SELECT * FROM qryTest1
WHERE question_number = 'Q1'
</cfquery>


Because I also need to display other questions too. Also qryTest2 queries a table that has valid answer values for only Q1. whereas qryTest1 display all answer values for Q1 and valid answers for other questions. So need to have both the queries and then filter Q1 answer values doing ListFind() or something.

M
MaryJo
Inspiring
August 6, 2006
quote:
Because I also need to display other questions too.

Then you just do multiple Q-of-Qs.


quote:
Also qryTest2 queries a table that has valid answer values for only Q1. whereas qryTest1 display all answer values for Q1 and valid answers for other questions. So need to have both the queries and then filter Q1 answer values doing ListFind() or something.

I'm not really sure I understand what your tables are doing, it sounds like they are duplicating data, if both of them have the answers. Is there some data in qryTest2 that is not already in the qryTest1? In any case, if you need to filter a query recordset, Q-of-Q is ideal, it's what it was designed to do. Looping through the entire query and searching for just the records you want is not generally an efficient way to do it.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices