<!--- Get all open orders. --->
<cfquery name="qOrder" datasource="datasource1">
	SELECT
		o.id,
		o.date_ordered,
		o.supplier_id,
 
		<!--- Set default supplier values. --->
		( '' ) AS supplier_name,
		( '' ) AS supplier_phone,
		( '' ) AS supplier_location
	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>
 
 
<!--- Loop through the orders and add supplier. --->
<cfloop query="qOrder">
 
	<!---
		Only check for supplier if we have a supplier ID
		to act on. Otherwise, leave the default values ('').
	--->
	<cfif qOrder.supplier_id>
 
		<!---
			We have a supplier. Try to find it in the
			suppliers query: get the row index of the
			first matching id.
		--->
		<cfset intIndex = qSupplier[ "id" ].IndexOf(
			JavaCast( "int", qOrder.supplier_id )
			) />
 
		<!--- Add one to the index to make it CF-Friendly. --->
		<cfset intIndex = (intIndex + 1) />
 
		<!--- Check to see if we found a match. --->
		<cfif intIndex>
 
			<!--- Update cell values. --->
			<cfset qOrder[ "supplier_name" ][ qOrder.CurrentRow ] =
				qSupplier[ "name" ][ intIndex ]
				/>
 
			<cfset qOrder[ "supplier_phone" ][ qOrder.CurrentRow ] =
				qSupplier[ "phone" ][ intIndex ]
				/>
 
			<cfset qOrder[ "supplier_location" ][ qOrder.CurrentRow ] =
				qSupplier[ "location" ][ intIndex ]
				/>
 
		</cfif>
 
	</cfif>
 
</cfloop>