Solved

help looping thru query results

Posted on 2002-03-14
2
157 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

21 Experts available now in Live!

Get 1:1 Help Now