9 Replies Latest reply on Jul 13, 2010 2:41 PM by HappyHour-SC

    Multiple queries inside each other ?? Possible?

    HappyHour-SC

      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>

        • 1. Re: Multiple queries inside each other ?? Possible?
          ilssac Level 5

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

          • 2. Re: Multiple queries inside each other ?? Possible?
            ilssac Level 5

            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.

            • 3. Re: Multiple queries inside each other ?? Possible?
              HappyHour-SC Level 1

              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?

              • 4. Re: Multiple queries inside each other ?? Possible?
                Dan Bracuk Level 5

                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.

                • 5. Re: Multiple queries inside each other ?? Possible?
                  HappyHour-SC Level 1

                  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.

                  • 6. Re: Multiple queries inside each other ?? Possible?
                    Reed Powell Level 3

                    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

                    • 7. Re: Multiple queries inside each other ?? Possible?
                      Reed Powell Level 3

                      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

                      • 8. Re: Multiple queries inside each other ?? Possible?
                        HappyHour-SC Level 1

                        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.

                        • 9. Re: Multiple queries inside each other ?? Possible?  - CLOSED !!
                          HappyHour-SC Level 1

                          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.