Coldfusion looping over query and display html tag only one time

Question

I have a problem with looping over sql queries and if the query is not null then i must display some html tag. The problem is that let's say that query find 3 items, it will display 3x file instead of 1x.

Here is my code:

<cfquery name="client_id" datasource="#application.dsn#">
    Select borrower_id,client_id,id from contracts where borrower_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(url.ID)#">
</cfquery>

<cfloop query="client_id">
    <cfquery name="Payment_log" datasource="#application.dsn#">
        SELECT *
        FROM paymentLog
        WHERE contract_id = <cfqueryparam value="#client_id.id#" maxlength="36" cfsqltype="CF_SQL_BIGINT">
            AND DateDelete IS NULL
        ORDER BY date_log, id
    </cfquery>

    <cfloop query="Payment_log">

        <cfquery name="payment_log_file" datasource="#application.dsn#">
            SELECT file_name
            FROM paymentLog_file
            WHERE log_id = #Payment_log.id#
        </cfquery>

        <cfloop query="payment_log_file">

            <cfif payment_log_file.recordCount neq 0>
                <th>file name</th>
            </cfif>
        </cfloop>
    </cfloop>
</cfloop>

This is the result

<th>file name</th>
<th>file name</th>
<th>file name</th>

This is what i want/need

<th>file name</th>

Okay so I changed my sql query as suggested by Matt Busche and Dan Bracuk to this:

SELECT plf.log_id
FROM paymentLog pl
        LEFT JOIN paymentLog_file plf ON pl.id = plf.log_id
WHERE contract_id = <cfqueryparam value="#client_id.id#" maxlength="36" cfsqltype="CF_SQL_BIGINT">
        AND DateDelete IS NULL
ORDER BY plf.log_id, date_log, id

And I get this:

This is what i get

This is what i want/need

Answer 1

You'll want to try to go farther than I did and reduce this to one query overall if possible, but this should get you on the right track

Combine your two inner queries into one query, add an order by attribute on the duplicate row type, and then add a group by attribute to your cfloop. If you're on CF10? or lower you may need to use cfoutput instead of cfloop to use the group attribute

<cfquery name="client_id" datasource="#application.dsn#">
 Select borrower_id,client_id,id from contracts where borrower_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(url.ID)#">
</cfquery>

<cfloop query="client_id">
  <cfquery name="Payment_log" datasource="#application.dsn#">
    SELECT pl.log_id log_id
    FROM paymentLog pl
        INNER JOIN paymentLog_file plf ON pl.log_id = plf.log_id
    WHERE contract_id = <cfqueryparam value="#client_id.id#" maxlength="36" cfsqltype="CF_SQL_BIGINT">
        AND DateDelete IS NULL
    ORDER BY pl.log_id, date_log, id
 </cfquery>

  <cfloop query="Payment_log" group="log_id">
    <th>file name</th>
  </cfloop>
</cfloop>