• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Multiple queries inside each other ?? Possible?

New Here ,
Jul 12, 2010 Jul 12, 2010

Copy link to clipboard

Copied

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>

TOPICS
Advanced techniques

Views

1.7K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jul 12, 2010 Jul 12, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jul 12, 2010 Jul 12, 2010

Copy link to clipboard

Copied

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>

<cfoutput>

<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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 12, 2010 Jul 12, 2010

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

Thanks Dan.  I do have books and in all honesty, the Forums are usually the last place I go because I know

I can figure it out with all of my references.  Sometimes, the project at hand is a little more different or difficult as some of the examples in the books.  I'll check out that book too.   Thanks for your help and everyone else.

Sometimes, you reach a point that you (I) have to ask for help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

Try doing it all in one query:

<cfquery name="Checkclientnumber" datasource="allsales">

         SELECT clientsales.*,clients.lastname,clients.firstname

FROM clientsales,clients

         WHERE clientnumber = '#clientnumber#'

AND clients.clientnumber=clientsales.clientnumber

    </cfquery>

I think that your latest problem with the CFLOOP and query="client" is because you are calling it "client" which is a variables scope.  Try calling it "clientStuff" or whatever.

-reed

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

Even easier:  Just get rid of the nested CFOUTPUT - you don't need it.  You're inner CFQUERY is only returning 1 row, so you don't need to loop over it.  You're already inside of a CFOUTPUT, so the #xxx# variables will be interpreted correctly.

-reed

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

Thanks for all your help...  in the same course I learned something..   The last answer with the tablename.fieldname,tablename.fieldname calls multiple tables in one query.

It did pull the data, but I am trying to figure out why the 2nd call didn't show the users names.  It shows the items purchased and users ID, but the lastname,firstname doesn't appear.  I am sure I should be able to figure it out.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 13, 2010 Jul 13, 2010

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation