6 Replies Latest reply on May 18, 2007 3:14 PM by Manuel_Lino

    WHERE using results from another querie

    Manuel_Lino Level 1
      Hi,

      Example:
      <cfquery name="QUERY 1" datasource="MyDatabase">
      SELECT Table1ID, A, B, C
      FROM Table1
      WHERE A = #Form.A#
      <cfoutput query="QUERY1">#A#<br></cfoutput>
      It returns:
      3
      4

      <cfquery name="QUERY 2" datasource="MyDatabase">
      SELECT Table2ID, A, Z, Y
      FROM Table2
      WHERE A IN (3,4)
      <cfoutput query="QUERY 2">#Table2ID#</br></cfoutput>
      It returns:
      a
      b
      c
      d
      e
      f

      But if I make QUERY2 as:
      <cfquery name="QUERY 2" datasource="MyDatabase">
      SELECT Table2ID, A, Z, Y
      FROM Table2
      WHERE A IN (#QUERY1.A#)
      it returns only:
      a
      b
      c

      The values a,b,c are the Table2ID's where A=3
      The values d,e,f are the Table2ID'a where A=4

      How can I make return all values?

      Hope you understand.
      Thanks
      Manel
        • 1. WHERE using results from another querie
          paross1 Level 2
          (#QUERY1.A#) only contains the A column value from one row returned, since you aren't looping through the query to get all rows. In other words, it isn't a list. You may want to loop the the first query using cfloop or cfoutput and a query attribute to populate a list that you can then use in your second query.

          Phil
          • 2. Re: WHERE using results from another querie
            Manuel_Lino Level 1
            Thanks for your reply.

            I´ve tried this but it didn´t work. Please help.

            <cfquery name="QUERY 1" datasource="MyDatabase">
            SELECT Table1ID, A, B, C
            FROM Table1
            WHERE A = #Form.A#
            </cfquery>

            <cfloop query="QUERY1">
            <cfquery name="QUERY 2" datasource="MyDatabase">
            SELECT Table2ID, A, Z, Y
            FROM Table2
            WHERE A IN (#QUERY1.A#)
            </cfquery>
            </cfloop>

            Could you explain what i´m missing.
            It returns an error saying : Incorrect sytax near '3'

            Thanks
            Manuel
            • 3. WHERE using results from another querie
              paross1 Level 2
              Think about what you are doing for a second. You are selecting multiple rows of data with your first query. Then you are using a cfloop to loop through each row returned from that first query to perform another query that uses the value returned in the A column. The problem is that you are executing your second query once for each row returned by your first query, and you really aren't saving that result anywhere, since each iteration of the loop causes your cfselect to overwrite your previous results. You can create a list variable and populate it with the results from your first query, but I have to ask, unless you need the results from your first query for something else, why don't you just join the two tables and use on equery?

              Based on what you have, something like this?

              <cfquery name="QUERY 2" datasource="MyDatabase">
              SELECT T2.ID, T2.A, T2.Z, T2.Y
              FROM Table2 T2
              INNER JOIN Table1 T1 ON T2.A = T1.A
              WHERE T1.A = #Form.A#
              </cfquery>

              Phil
              • 4. Re: WHERE using results from another querie
                Manuel_Lino Level 1
                Yes, I do need it to return the results. The inner join is actually already done. How should I make the second query to return the values like in the example where I make the WHERE clause "WHERE A IN (3,4)". When I do this, I get the report I need grouping by 3 and 4.
                Could you explain how to get the list variable and use it in the second query? I'll appreciate.

                Thanks again Phil
                Manuel
                • 5. Re: WHERE using results from another querie
                  paross1 Level 2
                  I'm guessing something like this:

                  <cfquery name="QUERY1" datasource="MyDatabase">
                  SELECT Table1ID, A, B, C
                  FROM Table1
                  WHERE A = #Form.A#
                  </cfquery>

                  <CFSET your_list = ValueList(QUERY1.A)>

                  <cfquery name="QUERY2" datasource="MyDatabase">
                  SELECT Table2ID, A, Z, Y
                  FROM Table2
                  WHERE A IN (#your_list#)
                  </cfquery>

                  (You would use quotedvaluelist() and preservsinglequotes() if you wanted a list of non-numeric values.)

                  Phil
                  • 6. WHERE using results from another querie
                    Manuel_Lino Level 1
                    Thanks Phil.
                    Created:
                    <cfset temp = ValueList(QUERY1.Table1ID)>
                    and set the WHERE clause:
                    WHERE Table2ID IN (#temp#)
                    It worked beautifully

                    Thanks again :)

                    Manuel