how to approach this problem

        I am displaying a dynamic table  which consits of four columns named as Ingredient,Lot,d_amount,d_unit......out of which Ingredient,d_amount,d_unit are taken from one

query named as "qrecipes" i.e from one table and Lot Cloumn is taken from another table.Now the problem is the data that is to be displayed in lot column is dependent on the

data present in ingredient.i.e If the first row ingredient column has "XYZ" then i need to pick the id of "XYZ" from second table and display in lot column.

              Ingredient   Lot            d_amount     d_unit

        1   xyx       345 is id
                           for XYZ which
                          should be
            displayed from
            Second table

Below is my code ....please say what is wrong .....please suggest me if there are any other methods or any kind of solution to solve this problem

Queirs

<cfquery name="qrecipes" datasource="testmsexcell">
SELECT *
FROM Recipes WHERE product='#form.product#'

</cfquery>

<cfquery name="qlot"  datasource="testmsexcell">
SELECT Lots.id
FROM Lots WHERE ingredient='#qrecipes.ingredient#'
</cfquery>

Table

                               table border="2px" id="maketable">
                                    <tr>
                                     
                                      <td>Ingredient</td>
                                      <td>Lot</td>
                                      <td>d_amount</td>
                                      <td>d_unit</td>
                                    </tr>
                                    <cfoutput query="qrecipes">
                                      <tr>
                                      
                                        <td>#qrecipes.ingredient#</td>
                                        <td><cfselect name="lotid" id="lotid" query="qlot" value="id" display="id"></cfselect></td>
                                        <td>#qrecipes.d_amount#</td>
                                        <td>#qrecipes.d_unit#</td>
                                      </tr>
                                    </cfoutput>
                                  </table>