#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    70
    Rep Power
    0

    Simple Loop Query


    Have been on this project too long ... trying to reconcile credit card batch statement line items to actual orders. Data tables is on different dsn's.

    The parent query loads fine:
    <cfquery datasource="#request.datasource#" name="recon">
    SELECT *
    FROM Commerce
    WHERE
    (ID >= #(form.startdate)#) AND (ID <= #(form.enddate)#)
    Order By ID
    </cfquery>

    <cfoutput query="recon">

    <cfquery name="reg" datasource="data2">
    SELECT *
    FROM Alpiner
    WHERE Extra3 = '#recon.extra3#'
    Order By Extra3
    </cfquery>


    <table width="800" cellspacing="4" cellpadding="4" summary="" border="0">
    <tbody>
    ***********data from recon **************

    now want to display child records for each line above directly underneath each parent.

    </cfoutput>


    <cfoutput query="reg">

    <tr>
    <td>



    #s1fname# #extra3#</td></tr></cfoutput></tbody></table>

    In a nutshell ... Data from Commerce table alternating with data from Alpiner table ... have tried looping joins ... major league code blindness coupled with serious brain fart.

    Cheers ....
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Originally Posted by frapple
    Data tables is on different dsn's.
    All you should need is a JOIN. Depending on your database type and permissions, it may be possible to do it directly. Otherwise, try doing the JOIN in a QoQ.

    (ID >= #(form.startdate)#) AND (ID <= #(form.enddate)#)
    I am guessing that is psuedo-code ... But if you are really not using cfqueryparam - you should start. At the very least to help protect your financial data against sql injection.

IMN logo majestic logo threadwatch logo seochat tools logo