> There are two pertinent tables: QOrdLineItems and QHistRO. > The first query looks like this: > > <cfquery name="Recordset1" datasource="AR"> SELECT OrderNum, > Status, MainLine, acct_id, date, po_num FROM "QOrdLineItems" > WHERE acct_id = '#COOKIE.id#' ORDER BY date DESC </cfquery> > > My understanding is this pulls five variables from > QOrdLineItems when the variable acct_id matches the id in the > cookie being set when logged in. I have no idea what "ORDER > BY date DESC" does.
It sorts your recordset by date, in descending order (newest to oldest). Presumably, you might have multiple orders associated with a single account. And, as for the "five variables" bit, not exactly. Your query returns a single variable, named "Recordset1" (that's not a very descriptive name, so in the future you might use names that correspond with the meaning of the data in question). Within that variable, instead of having a single value, you have rows and columns. It might be helpful to visualize your recordset as a table: OrderNum | Status | MainLine | acct_id | date | po_num ---------------------------------------------------------------------------- ----------------------- 1 | shipped | ink | 1 | 2007-02-01 | 1 ---------------------------------------------------------------------------- ----------------------- 1 | shipped | paper | 1 | 2007-0201 | 1 ---------------------------------------------------------------------------- ----------------------- 2 | on hold | paper | 1 | 2007-02-03 | 2 ---------------------------------------------------------------------------- ----------------------- .... In your particular recordset, the acct_id column would have the same value for every row, because it's used as a filter in your WHERE clause. Assuming that there's more than one order per the account in question, you will have more than one row. Assuming that there's more than one line item for a single order, you will have multiple rows with the same order number. > The new reorder information I need to output is in the > QHistRO table and is in two different columns called > reorder_num and control_num. I've tried a bunch of differnet > ways to call these variables and none so far have worked, > although it may be my output statements that aren't working. > Currently I have this query: > > <cfquery name="Recordset2" datasource="AR"> SELECT > reorder_num, control_num FROM "QHistRO" WHERE reorder_num = > '#Recordset1.OrderNum#' OR control_num = '#Recordset1.OrderNum#' > </cfquery> > > I'm trying to pull the variables from the table only when the > reorder_num/control_num matches the OrderNum in Recordset1 > pulled from QOrdLineItems. Well, if you have multiple records in the first recordset, they'll have different order numbers, but your second query will only return the rows corresponding to one of those order numbers. That's probably not what you want. In your case, the best thing to do would probably be to rewrite your two queries as a single query. There are a couple ways this could play out, based on what's actually in your tables. Does every order have a corresponding record in the QHistRO table? If so, then you might have this: SELECT LI.OrderNum, LI.Status, LI.MainLine, LI.acct_id, LI.date, LI.po_num, H.reorder_num, H.control_num FROM QOrdLineItems LI INNER JOIN QHistRO H ON LI.OrderNum = H.reorder_num OR LI.OrderNum = H.control_num WHERE acct_id = <cfqueryparam cfsqltype="cf_sql_varchar" value="#COOKIE.id#"> ORDER BY date DESC Otherwise, you may have to use an outer join, to get the values from QOrdLineItems and only get matches from QHistRO if they exist. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! This email has been processed by SmoothZap - www.smoothwall.net ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272464 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
