How to create an if statement for a relational database in ColdFusion?

Question

How can I show a table row if a sub-task exists?

Database info:

• table1 tasks table
• table2 sub-task table

Some tasks have sub-tasks, so how do I create an if statement for the results table: if tasks table has a record in the sub-tasks table display the record below the task record.

<tr>
    <td> #taskName# <td>
</tr>
<cfif subtask exists display row>
    <tr>
         <td>#sub-taskName#</td>
    <tr>
</cfif>

Tried this:

<cfif taskID = sub-taskid>
   <tr>
      <td>#sub-taskName#</td>
   <tr>
</cfif>

Not sure if this is the best method and how to loop if more than 1 sub-tasks are present.

Answer 1

There is an example of how to do this at bottom of this page : https://cfdocs.org/cfoutput

use similar to this for sql query

<cfquery name="taskQuery" datasource="mydatasource">
    SELECT taskId, taskName, subTaskName
    FROM tasks
        RIGHT OUTER JOIN subtasks ON tasks.taskId = subtask.taskId
    ORDER BY taskWeight, taskId, subTaskWeight, subTaskId
</cfquery>

OR use this object to demo code

<cfset taskQuery = queryNew("taskId,taskName,subTaskName","int,varchar,varchar",
    [ {taskId:1, taskName:"Task 1",subTaskName:"Subtask 1.1"}, 
      {taskId:1, taskName:"Task 1",subTaskName:"Subtask 1.2"}, 
      {taskId:2, taskName:"Task 2",subTaskName:"Subtask 2.1"} ])>

then output using

<table>
<cfoutput query="taskQuery" group="taskId">
    <tr><td><h2>#taskName#</h2></td></tr>
    
    <cfoutput>
        <tr><td>#subTaskName#</td></tr>
    </cfoutput>
</cfoutput>
</table>