working but need to get that piece from mysql

103

January 15, 2021, at 11:50 AM

I have the following code which is working as expected,

I am editing my question again here

This is the loop i have

<cfquery name="qry">
SELECT * 
  FROM table1 
</cfquery>
<cfquery name="qrysell">
  SELECT *
  FROM table2
  WHERE SPID=<cfqueryparam value="#Val(qry.SPID)#">
</cfquery>
    
<ul>
    <cfloop query="qrysell">
        <cfquery name="insidequery">
          SELECT SPCPID
          FROM table3
          WHERE SPOID=<cfqueryparam value="#Val(qrysell.SPOID)#">
        </cfquery>
        <li><a href="">#qrysell.Text1# <small>(#qrysell.SPOID#)</small> | #Val(insidequery.RecordCount)#</a></li>
    </cfloop>
</ul>

instead all i am trying to do is just bring the loop data in mysql and this is where i am some doubts that how should i do?

i already had a loop like this

<cfquery name="qry">
    SELECT * 
      FROM table1 
      left join table2 
        ON table2.SPID = table1.SPID 
      left join table3 
        on table3.SPOID = table2.SPOID 
    </cfquery>

i am trying to adjust the code in first block to be directly being generated in the second query and i am bit lost what is wrong i am doing

Answer 1

SELECT table1.id, table1.column1, sales.salesTotal
FROM table1
INNER JOIN (
    SELECT COUNT(dkid) AS salesTotal, spoid 
    FROM table2 GROUP BY spoid
) sales ON sales.spoid = table1.spid

This should return a single query like so:

id  |  column1  |  salesTotal

Which then you can loop over:

<td>
    <ul>
        <cfloop query="qry">
            <li>
                 <a href="#>#column1# <small>(#id#)</small> | #Val(salesTotal)# users</a>
            </li>
        </cfloop>
    </ul>
</td>

Is that what you are asking how to do?