Re: query of queries or something else
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
Re: query of queries or something else
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
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:311468 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: query of queries or something else
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
