How do you NOT use a query of queries inside a loop to solve the problem of having to build a select list for each row?
In this example, every customer has a list of actions unique to that customer:
<cfquery name="qryAction" datasource="myDataSource">
SELECT ActionID,CustID,ActionName FROM AvailableActions
</cfquery>
<cfquery name="qryOrderHeader" datasource="myDataSource">
SELECT CustID FROM OrderHeader
</cfquery>
<html>
<body>
<cfform preservedata="yes">
<cfloop query="qryOrderHeader">
<cfquery name="qry3" dbtype="query">
SELECT ActionID,ActionName FROM qryAction
WHERE CustID = #qryOrderHeader.CustID#
</cfquery>
<cfselect name="ActionID" query="qry3" display="ActionName" value="ActionID" />
</cfloop>
</cfform>
</body>
</html>
Here's the SQL if it helps illustrate my example.
use tempdb
GO
create table Cust(
CustID Int Identity Primary Key,
CustName Varchar(255)
)
GO
INSERT INTO Cust(CustName) values('One')
INSERT INTO Cust(CustName) values('Two')
GO
create table AvailableActions(
ActionID Int Identity Primary Key,
CustID Int,
ActionName Varchar(255)
)
GO
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Insert')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Edit')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Delete')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Insert')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Edit')
GO
CREATE TABLE OrderHeader(
OrderHeaderID Int Identity Primary Key,
CustID Int
)
INSERT INTO OrderHeader(CustID) VALUES(1)
INSERT INTO OrderHeader(CustID) VALUES(2)
INSERT INTO OrderHeader(CustID) VALUES(2)
I suppose the best result would be to include ActionID and ActionName in qryOrderHeader.