CFQUERY - Calling results from one to supply inputs foranother

[jvanlaere]

Here's my code...

------------------------------------

<cfquery name="Recordset1" datasource="DB1">
SELECT *
FROM dbo.tbl_Main
LEFT JOIN dbo.tbl_Order
ON dbo.tbl_Main.Reference_Code = dbo.tbl_Order.Reference_Code
WHERE dbo.tbl_Main.Prep_Date <> '1/1/1900' AND
dbo.tbl_Main.Fleet_Delivery_Date IN ('1/1/1900',' ')
ORDER BY dbo.tbl_Main.Stock_Number
ASC
</cfquery>

<cfquery name="Recordset2" datasource="DB2">
SELECT * FROM dbo.Vehicle WHERE StockNo = '#Recordset1.Stock_Number#'
</cfquery>

<table width="600" border="0" cellspacing="1" cellpadding="4">
<tr>
<td width="75"><u><strong><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">Stock
#</font></strong></u></td>
<td width="75"><u><strong><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">Serial
#</font></strong></u></td>
<td width="75"><u><strong><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">GSO</font></strong></u></td>
<td width="75"><u><strong><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">Model</font></strong></u></td>
<td width="100"><u><strong><font size="2" face="Tahoma, Verdana,
Helvetica, sans-serif">Received</font></strong></u></td>
<td width="100"><u><strong><font size="2" face="Tahoma, Verdana,
Helvetica, sans-serif">Prepped</font></strong></u></td>
<td width="100"><u><strong><font size="2" face="Tahoma, Verdana,
Helvetica, sans-serif">Picked
Up</font></strong></u></td>
</tr>

<cfloop query="Recordset1"> <cfoutput>
<tr>
<td><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">#Recordset1.Stock_Number#</font></td>
<td><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">#Recordset2.VehID#</font></td>
<td><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">#Recordset1.GSO_Number#</font></td>
<td><font size="2" face="Tahoma, Verdana, Helvetica,
sans-serif">#Recordset2.Model#</font></td>
<td>
<font size="2" face="Tahoma, Verdana, Helvetica, sans-serif">
<cfif #Recordset1.NTG_Delivery_Date# EQ "01/01/1900">
- - - -
<cfelse>
#LSDateFormat(Recordset1.NTG_Delivery_Date,'M/DD/YY')#</font>
</cfif>
</td>
<td>
<font size="2" face="Tahoma, Verdana, Helvetica, sans-serif">
<cfif #Recordset1.Prep_Date# EQ "01/01/1900">
- - - -
<cfelse>
#LSDateFormat(Recordset1.Prep_Date,'M/DD/YY')#</font>
</cfif>
</td>
<td>
<font size="2" face="Tahoma, Verdana, Helvetica, sans-serif">
<cfif #Recordset1.Fleet_Delivery_Date# EQ "01/01/1900">
- - - -
<cfelse>
#LSDateFormat(Recordset1.Fleet_Delivery_Date,'M/DD/YY')#</font>
</cfif>
</td> </tr>
</cfoutput>
</cfloop>


-----------------------------

I am trying to pull information from two databases via two separate, yet
incorporated cfquery.
Yet the output gives me the same repeated field value for

Recordset2.Model and Recordset2.VehID

I want those values to change based on which Stock_Number is running in the
first query.

IT OUTPUTS LIKE THIS:

Stock # Serial # GSO Model Received Prepped Picked Up
7340-N 6M026642 20375 MR688S 4/19/05 4/26/05 - - - -
7520-N 6M026642 MR688S 3/23/05 3/24/05 - - - -
7589-N 6M026642 20371 MR688S 4/21/05 4/29/05 - - - -
7698-N 6M026642 MR688S 4/01/05 4/11/05 - - - -
7877-N 6M026642 22291 MR688S 3/07/05 3/08/05 - - - -
8223-N 6M026642 MR688S 4/05/05 4/11/05 - - - -

I would like the serial # and models to change with the changing stock #s.

[The ScareCrow]

Your main problem is that you are looping through recordset1, but only using
the first record of recordset2

First does the first query give you the correct results ?

I'm not sure I understand the "AND" clause. I would only ever have 2 values
in a date column, a valid date or NULL
I would also pass the data as an ODBC date type, so the clauses would be

WHERE dbo.tbl_Main.Prep_Date <> #CreateODBCDate(1/1/1900)#
AND (dbo.tbl_Main.Fleet_Delivery_Date = #CreateODBCDate(1/1/1900)# OR
dbo.tbl_Main.Fleet_Delivery_Date Is Null)


Next, Do you need 2 queries or can they be combined ? If you can combine
them, then

SELECT *
FROM dbo.tbl_Main
LEFT JOIN dbo.tbl_Order
ON dbo.tbl_Main.Reference_Code = dbo.tbl_Order.Reference_Code
INNER JOIN dbo.Vehicle ON dbo.Vehicle.StockNo = dbo.tbl_Main.Stock_Number
WHERE dbo.tbl_Main.Prep_Date <> #CreateODBCDate("1/1/1900")#
AND (dbo.tbl_Main.Fleet_Delivery_Date = #CreateODBCDate("1/1/1900")# OR
dbo.tbl_Main.Fleet_Delivery_Date Is Null)
AND dbo.Vehicle.StockNo = '#Recordset1.Stock_Number#'

I would also remove the "*" from the select list and list the actual columns
you need.

Depending upon the returned result set, you may need to change the INNER JOIN
to an OUTER JOIN to get the correct results.

Ken

[jvanlaere]

Thanks for the response, Ken.
The first result is correct, so that is indicating a correct pull.

I do not think I can combine the queries because they from two different datasources.

[jvanlaere]

I figured it out...

<cfquery name="Recordset1" datasource="DB1">
SELECT *
FROM dbo.tbl_Main
LEFT JOIN dbo.tbl_Order
ON dbo.tbl_Main.Reference_Code = dbo.tbl_Order.Reference_Code
WHERE dbo.tbl_Main.Prep_Date <> #CreateODBCDate("1/1/1900")#
AND (dbo.tbl_Main.Fleet_Delivery_Date = #CreateODBCDate("1/1/1900")# OR
dbo.tbl_Main.Fleet_Delivery_Date Is Null)
ORDER BY dbo.tbl_Main.Stock_Number
ASC
</cfquery>

<cfquery name="Recordset2" datasource="DB2">
SELECT * FROM dbo.Vehicle
</cfquery>

<!--- Combine the results --->
<CFQUERY NAME="combined" DBTYPE="query">
SELECT *
FROM Recordset1, Recordset2
WHERE Recordset2.StockNo = Recordset1.Stock_Number
</CFQUERY>

Thanks!

[The ScareCrow]

Because of the syntax your using, I'm assuming the db is sql server. If this
is the case, then you can create a stored procedure to return the required
recordset. This would then enable you to combine the queries into one query
and only have to have the single db call in cf.

Ken