How about something like this: <cfquery name="qryGetOtherProperties" datasource="#appDSN#"> SELECT f.AssetID, f.name, i.text, i.amount FROM FixedAssets f INNER JOIN InvoicesWork i ON i.AssetID = f.AssetID WHERE f.JobID = "#qryGetInvoice.JobID#" AND f.AssetID IN (select AssetID FROM InvoiceWork WHERE .... condition to get assets from InvoiceWork that you want ) AND f.AssetID NOT IN (select AssetID FROM InvoiceWork WHERE .... condition to get assets from InvoiceWork that you DO NOT want ) </cfquery>
I'm guessing on a few things here, but if you list out your table and columns that we're talking about here and confirm the conditions, I'm sure you could get this all in one query instead of using QofQ (unless of course you're talking about different datasources, which it doesn't look like you are). Dave Phillips http://www.dave-phillips.com/ -----Original Message----- From: Andrew Whone [mailto:[EMAIL PROTECTED] Sent: Saturday, August 23, 2008 3:47 PM To: CF-Talk Subject: query of queries or something else If a qry a couple of tables: <!---get Job Property details ---> <CFQUERY Name="qryGetJobProperties" datasource="#appDSN#"> SELECT AssetID, Name FROM FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#" </CFQUERY> <cfset PropertyFieldID = ValueList(qryGetJobProperties.AssetID)> <!---get invoice Property details ---> <CFQUERY Name="qryGetInvoiceProperties" datasource="#appDSN#"> SELECT AssetID, Text, Amount FROM InvoicesWork WHERE InvoiceID="#qryGetInvoice.InvoiceID#" </CFQUERY> I now need a query that will extract any Asset from the Table FixedAssets that is included in qryGetJobProperties but not in qryGetInvoiceProperties so that I can display it in a dynamic form. Something like this; <CFQUERY Name="qryGetOtherProperties" dbtype="query"> SELECT qryGetJobProperties.AssetID, qryGetJobProperties.Name, qryGetInvoiceProperties.AssetID FROM qryGetJobProperties, qryGetInvoiceProperties WHERE qryGetInvoiceProperties.AssetID != qryGetJobProperties.AssetID </CFQUERY> I have also tried Nested Loops and Unions but am really struggling, everything I try doesn't seem to make sense. I could mess about with an array but is there really any need? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311469 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
