>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/ > >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:311472 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
