<!--- Get all open orders. --->
<cfquery name="qOrder" datasource="datasource1">
	SELECT
		o.id,
		o.date_ordered,
		o.supplier_id
	FROM
		orders o
	WHERE
		o.is_open = 1
</cfquery>
 
<!--- Get suppliers for given orders. --->
<cfquery name="qSupplier" datasource="datasource2">
	SELECT
		s.id,
		s.name,
		s.phone,
		s.location
	FROM
		suppliers s
	WHERE
		id IN ( #ValueList( qOrder.suplier_id )# )
</cfquery>
 
 
<!--- Set default array. --->
<cfset ColumnValues = ArrayNew( 1 ) />
 
<!--- Add new columns to the open orders query. --->
<cfset temp = QueryAddColumn(
	qOrder,
	"supplier_name",
	ColumnValues
	) />
 
<cfset temp = QueryAddColumn(
	qOrder,
	"supplier_phone",
	ColumnValues
	) />
 
<cfset temp = QueryAddColumn(
	qOrder,
	"supplier_location",
	ColumnValues
	) />
 
<cfset theCurrentRow = 1 />
 
<!--- Loop through the orders and add supplier. --->
<Cfloop query="qOrder">
 
	<cfquery name="qCheckSupplier" dbtype="Query">
		SELECT
			name,
			phone,
			location
		FROM
			qSupplier
		WHERE
			id = '#trim( qOrder.supplier_id )#'
	</cfquery>
 
	<cfif (
		trim(qOrder.supplier_id) IS NOT "" AND
		ISNumeric(trim(qOrder.supplier_id))
		)>
 
		<cfset qOrder[ "supplier_name" ][ theCurrentRow ] = "#qCheckSupplier.name#" />
		<cfset qOrder[ "supplier_phone" ][ theCurrentRow ] = "#qCheckSupplier.phone#" />
		<cfset qOrder[ "supplier_location" ][ theCurrentRow ]= "#qCheckSupplier.location#" />
 
	<cfelse>
 
		<cfset qOrder[ "supplier_name" ][ theCurrentRow ] = "" />
		<cfset qOrder[ "supplier_phone" ][ theCurrentRow ] = "" />
		<cfset qOrder[ "supplier_location" ][ theCurrentRow ] = "" />
 
	</cfif>
 
	<cfset theCurrentRow = incrementvalue( theCurrentRow ) />
 
</cfloop>