Solved

help looping thru query results

Posted on 2002-03-14
2
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

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…
What You Need to Know when Searching for a Webhost Provider
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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