Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

help looping thru query results

Posted on 2002-03-14
2
Medium Priority
?
193 Views
Last Modified: 2013-12-24
I am not getting the results that I want an would like to know if some one could help.

this first query returns a multiple of records based on a value that is passed from a form.

     <cfquery name="View_Cross_check" datasource="show">
SELECT     CompvisitNumber, competitor
FROM         dbo.competitor
WHERE     (competitor LIKE '#form.CompanyName#')
</cfquery>

in my example I had returned a recordcount of 6 individuals for this company like this

(competitors)
1 2 3 4 5 6.


now my issue is using the above records returned must now query using the following to get the remaining information.

<cfquery name="View_cross_check_results" datasource="show">
SELECT     visitor_Co_Name, BT_Booth1, visitorNumber
FROM         visitor_INFO INNER JOIN
                      Booth_Information ON visitorNumber = BT_visitorNumber
WHERE     (vistiorNumber = '#view_cross_check. competitor#')
</cfquery>

all I want is to return the name, booth1 and vistornumber to a table.

what I am receiving is that it is only showing up with 1 record when I know that there is six.

I have learned how to break this up if it was a commadelimited string but dont know how to loop with indiv records.

help




0
Comment
Question by:jriver12
2 Comments
 
LVL 6

Accepted Solution

by:
reitzen earned 200 total points
ID: 6865803
There are a couple of ways to do this, the one you choose depends on what you are comfortable with, the results you are trying to obtain, and your data structure.

The first and most efficient way is to return all the fields you are looking for in one query by joining all of the necessary tables together:

SELECT
  C.CompVisitNumber
, C.Competitor
, V.visitor_Co_Name
, V.visitorNumber
, B.BT_Booth1
FROM
  Competitor C
  INNER JOIN
  visitor_INFO V
    on C.competitor = V.visitorNumber
  INNER JOIN
  Booth_Information B
    on V.visitorNumber = B.BT_visitorNumber
WHERE
  C.competitor LIKE '%#form.CompanyName#%'

(You may need to change your joins based on your data structure)

The second way is to place your query results into a comma-delimited string and use the IN operator:
<CFSET Variables.sResultString = ValueList(view_cross_check.competitor,",")>

SELECT
  visitor_Co_Name
, BT_Booth1
, visitorNumber
FROM
  visitor_INFO
  INNER JOIN
  Booth_Information
    ON visitorNumber = BT_visitorNumber
WHERE
  vistiorNumber IN (#view_cross_check. competitor#)


And the final way is to wrap your query inside of a <CFOUTPUT>:

<CFOUTPUT QUERY="View_Cross_check">
<cfquery name="View_cross_check_results" datasource="show">
SELECT
  visitor_Co_Name
, BT_Booth1
, visitorNumber
FROM
  visitor_INFO
  INNER JOIN
  Booth_Information
    ON visitorNumber = BT_visitorNumber
WHERE
  vistiorNumber IN (#view_cross_check. competitor#)
</cfquery>
</CFOUTPUT>

Submit another post if you have any trouble or need more details.
0
 

Author Comment

by:jriver12
ID: 6865978
I should have thought of that solution(your first) before getting crazy.

I must remember to k.i.s.s.

Thanks a mil :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month9 days, 20 hours left to enroll

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question