Known Participant

Question

Multiple queries inside each other ?? Possible?

Forum|Forum|15 years ago
July 12, 2010
3 replies
2204 views

I am doing a query on a table that pulls data like the customers number. Than, on my output on screen, I need to DISPLAY the clients name from the customers number. However, the clients name is inside another table under clientsinfo.

I need the output to look like this: Client Number: 123 (John Smith)

This doesn't work below? Any idea?

<cfquery name="Checkclientnumber" datasource="allsales">
         SELECT * FROM clientsales
         WHERE clientnumber = '#clientnumber#'
    </cfquery>

<cfoutput query ="checkclientnumber">Client Number: #clientnumber#

                <cfquery name="client" datasource="clientsinfo">
                        SELECT lastname, firstname FROM clients
                        WHERE clientnumber = '#clientnumber#'
                 </cfquery>

<cfoutput query="client"> ( #firstname# #lastname# )</cfoutput>

</cfoutput>

Advanced techniques

This topic has been closed for replies.

H

HappyHour-SCAuthor

Known Participant

FYI:

The reason (still odd) the lastname,firstname didn't appear on my display is both tables had these fields. The sales tables were blank where the client table had obviously their name. I had to delete the fields - lastname,firstname From the sales table (which is the 1st table) and I did a select * which probably override the client.firstname... etc..

THANK YOU !!! I will keep an eye out to help others too.

ilssac

Inspiring

Other then you can not nest two <cfouput query...> loops like that.

Try

<cfquery name="Checkclientnumber" datasource="allsales">
         SELECT * FROM clientsales
         WHERE clientnumber = '#clientnumber#'
     </cfquery>

<cfloop query ="checkclientnumber">Client Number: #clientnumber#

                <cfquery name="client" datasource="clientsinfo">
                         SELECT lastname, firstname FROM clients
                        WHERE clientnumber = '#clientnumber#'
                 </cfquery>

<cfloop query="client"> ( #firstname# #lastname# )</cfloop>

</cfloop>

</cfoutput>

But I would really try not to loop over queries like that. It is often a poor solution.

You may want to look into using a IN clause in a single query with the ColdFusion valueList() function, if you can not make use of some type of JOIN clause.

H

HappyHour-SCAuthor

Known Participant

I get this error message:

Attribute validation error for tag cfloop.
The value of the attribute query, which is currently client, is invalid.

The error occurred in xxx\managerview.cfm: line 96
94 : <cfoutput> 95 : 96 : <cfloop query = "client"> 97 : 98 : <cfquery name="client" datasource="data">

I am not sure what to do or the best way of doing this... again, I have two tables - One is a ClientInfo and the Other is Sales. When I run a report, I need to query the list of sales of that day - query the sales and it only shows the client id.. I need to get the client id and convert to the clients name. So, sales for the day will have 50+ transacations, which will show client id plus the clients name.

What do you think?

D

Dan_Bracuk

Inspiring

Your specific error is caused by looping through a query before you run it.

On a more general note, you only need a single query for what you are trying to do. If you don't know how to select from more than one table, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

ilssac

Inspiring

How does it not work? Errors, wrong data, hums out of tune?

Attribute validation error for tag cfloop.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded