>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/ > Thanks for the reply but it doesn't work, If we have 3 different Assets, all sharing the same JobID, two of which have the same InvoiceID (The other is not in the InvoicesWork table) and we employ the qry...
<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 FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#") </cfquery> The output is only the two assets with an InvoiceID (In the InvoicesWork table. It should be all three If we employ this qry... <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 FixedAssets WHERE FixedAssets.JobID="#qryGetInvoice.JobID#") AND f.AssetID NOT IN (SELECT AssetID FROM InvoicesWork WHERE InvoiceID="#qryGetInvoice.InvoiceID#") </cfquery> the output is empty and I want it to be the Asset not in the InvoicesWork table I suspect the join isn't working because the tables don't share the same field names but I dunno. Here's the tables: The Fixed Asset Table: CREATE TABLE `FixedAssets` ( `AssetID` smallint(5) NOT NULL auto_increment, `JobID` smallint(5) default '0', `Name` varchar(20) default NULL, PRIMARY KEY (`AssetID`), KEY `jobidx` (`JobID`) ) TYPE=InnoDB; The InvoicesWork Table: CREATE TABLE `InvoicesWork` ( `AssetID` smallint(5) default '0', `InvoiceID` smallint(5) NOT NULL, `Amount` float(10,2) default NULL, `Text` varchar(75) NOT Null, KEY `estidx`(`InvoiceID`) ) TYPE=InnoDB; waddya think? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:311473 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
