Trying to recreate the serverside part of a Flash game that runs on ColdFusion.

One thing that's making me stuck is that some functions are supposed to return an object that contains a recordset (results from a query) that contains *another* recordset, and I'm stumped on how to properly do that. Can someone give me a "for retards" guide? I got recordsets in objects down, but not recordsets in recordsets

My local environment using PostgreSQL with ColdFusion 2018, if that matters

Well I decided to try *something*

[code]
<cfquery name="getMissionData" datasource="HistoryDB">
SELECT *
FROM "Missions"
WHERE "MissionID" = #arguments.missionId#
</cfquery>

<cfquery name="getClues" datasource="HistoryDB">
SELECT c.*
FROM "Clues" c
INNER JOIN "Mission_Clues" mc ON c."ClueID" = mc."ClueID"
WHERE mc."MissionID" = #arguments.missionId#
</cfquery>

<cfloop query="getClues">
<cfquery name="getOptions" datasource="HistoryDB">
SELECT o."optionID", o."optionValues"
FROM "ClueOptions" o
INNER JOIN "Clues_Options" co ON o."optionID" = co."optionID"
WHERE co."ClueID" = #ClueID#
</cfquery>

<cfloop query="getOptions">
<cfquery name="getValues" datasource="HistoryDB">
SELECT v."copy", v."valueDefinition"
FROM "ClueOptions" v
WHERE v."optionID" = #optionID#
</cfquery>

<cfset getOptions.optionValues = getValues>
</cfloop>

<cfif getOptions.recordCount GT 0>
<cfset getClues.ClueOptions = getOptions>
</cfif>
</cfloop>
[/code]

It *works*, but is it REALLY best practice here?

Scratch that, I discovered a big flaw: clues get multiple times, in fact, as many times as there are optionValues.

Maybe INNER JOIN isn't the answer?

>>1530400
Actually, I misspoke. It's duplicate optionIDs that get added whenever they have multiple value definitions associated with them