6 Replies Latest reply on Jul 12, 2010 11:48 AM by cybertek23

    query a query

    cybertek23 Level 1

      I am having a nightmare with this query. I am trying to use the results from 1 query and then use it in the second but it will only give me 1 result when the it should give me 5.  The first query returns 5 ids so the second should give me the same

       

      <cfquery name="getquote" datasource="conveyancingshoppers">
      select solicitorsid from UUID
      where UUID='#UUID#'
      </cfquery>
      <cfoutput query="getquote
      ">
             
      <CFQUERY name="getname" datasource="conveyancingshoppers">
          SELECT *
        FROM solicitors
        WHERE solicitorsid = #getquote.solicitorsid#

      </CFQUERY>
      </cfoutput>

      I have tried so many different ways and cannot get it to work. In the second query i want to be able to select solicitorsid from the tables solicitors and fees.

      Please help me fix this

        • 1. Re: query a query
          ilssac Level 5

          I hear great things about books like "Teach yourself SQL in 10 minutes".

           

          Did you try

           

          <cfquery name="getquote" datasource="conveyancingshoppers">
               SELECT * 'this is a very bad habit
               FROM solicitors INNER JOIN uuid ON (solicitors.solicitorsid = uuid.solicitorsid)
               WHERE UUID='#UUID#'
          </cfquery>
          
          
          <cfdump var="#getQuote#">
          
          • 2. Re: query a query
            Dan Bracuk Level 5

            Ian gave you the best available answer for situations where the two tables have the same datasource.  If they don't, you might still need the book Ian mentioned.  Instead of the equal sign, you use the sql keyword "in".

             

            The coldfusion valuelist() function will also come in handy.

            • 3. Re: query a query
              cybertek23 Level 1

              Thanks that works for the 2 tables but i need to add another I have tried

               

              <cfquery name="getquote1" datasource="conveyancingshoppers">
                   SELECT *
                   FROM solicitors INNER JOIN uuid ON (solicitors.solicitorsid = uuid.solicitorsid)
                INNER JOIN uuid ON (fees.solicitorsid = uuid.solicitorsid)
                   WHERE UUID='#UUID#'
              </cfquery>

               

              but it gives me the error

               

               

              Error Executing Database Query.

              [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(solicitors.solicitorsid = uuid.solicitorsid) INNER JOIN uuid ON (fees.solicitorsid = uuid.solicitorsid)'.

               

              Is there something else i need

              • 4. Re: query a query
                ilssac Level 5
                <cfquery name="getquote1" datasource="conveyancingshoppers">
                   SELECT * 
                   FROM solicitors INNER JOIN
                        uuid ON (solicitors.solicitorsid = uuid.solicitorsid)INNER JOIN
                        fees ON (fees.solicitorsid = uuid.solicitorsid)
                   WHERE UUID='#UUID#'
                </cfquery>
                

                 

                You had linked the uuid table twice and not linked the fees table in your FROM clause.

                • 5. Re: query a query
                  ilssac Level 5

                  This should not affect the results at all, but since the UUID table seems to be the driving table of this query, I would probably right it this way to be a little more clear.

                   

                  <cfquery name="getquote1" datasource="conveyancingshoppers">
                     SELECT *
                     FROM uuid INNER JOIN
                         
                  solicitors ON (uuid.solicitorsid = solicitors.solicitorsid)INNER JOIN
                          fees ON (uuid.solicitorsid = fees.solicitorsid)
                     WHERE UUID='#UUID#'
                  </cfquery>
                  • 6. Re: query a query
                    cybertek23 Level 1

                    I have worked out how to completed it

                     

                    <cfquery name="getcombined" datasource="conveyancingshoppers">
                    SELECT s.*,f.*,u.*
                    FROM solicitors s, fees f , UUID u
                    WHERE u.UUID='#UUID#'
                    AND u.solicitorsid = s.solicitorsid
                    AND s.solicitorsid = f.solicitorsid

                    </cfquery>

                     

                    thanks for you help