Solved

help looping thru query results

Posted on 2002-03-14
2
144 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 50 total points
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now