• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

help looping thru query results

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
jriver12
Asked:
jriver12
1 Solution
 
reitzenCommented:
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
 
jriver12Author Commented:
I should have thought of that solution(your first) before getting crazy.

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

Thanks a mil :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now