-----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Subject: RE: Having trouble getting variables from different tables to out put
It sorts your recordset by date, in descending order (newest to oldest). Presumably, you might have multiple orders associated with a single account. -- OK, that makes sense. I was thinking they might be individual variables... 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: -- Well, like I said, I inherited this job and I'm certain I would've been more descriptive... And will be in the future. That description clears things up a little. I wasn't sure what use Recordset1 was if I'm pulling all those variables out but you are pulling the variables out and placing them in another table. 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. -- No, I hadn't thought about it that way. 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: -- Every order SHOULD have at least one of the two variables reorder_num or control_num. The company has changed billing policies a few times so some older orders may not have one or the other. 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 -- What do the LI. And H. indicate? I see you have FROM QOrdLineItems LI and QHistRO H which leads me to believe that SELECT simply sets up the table "headers" and it populates the LI. Variables from QOrdLineItems and the H. items from QHistRO only when OrderNum = reorder_num or OrderNum = control_num when the acct_id equals the cookie. That's my guess... I'm not sure how since I don't know what all the extra letters do but I'll try that in the morning. Thanks, Mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272490 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
