-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 err, you'd need to tack "from table1, table2" on the end of that obviously ;)
Toby On Tuesday 01 July 2003 11:12, you wrote: > I'm a little out of it with a cold but would something like this acheive > what you're looking for? (there's probably a cleaner way of doing it ;) ) > > select (select count(table1.value) > from table1 > left inner join table2 on table1.value = table2.value > ) as inCommonCount, > (select count(table1.value) > from table1 where table1.value not in (select table2.value > from table2 > ) > ) as nullCount > > cheers, > Toby > > On Tuesday 01 July 2003 09:29, you wrote: > > Okay, I'll have a go at this one from a different angle. > > > > In the thread below, I asked how to read NULL as an explicit value in CF. > > Given that CF can't do this, comments please if this is a better > > approach. > > > > Suppose I have two tables, table1 and table2. The key records in table2 > > are a subset of the key records of table1. I want to increment an array > > counter for every instance of the key record in table1 NOT being in > > table2, and to increment a different array for the values in common. > > > > I wanted to manage this all in the one CFQUERY, but it appears I'll need > > two of them. The following is pseudocode. > > > > CFQUERY CountNulls > > (Left/Right Join table1 and table2) > > (Where result is null) > > > > CFQUERY CountOthers > > (Left/Right Join table1 and table2) > > (Where result is not null) > > > > CFLOOP CountNulls > > Increment CountNullsArray[x] > > > > CFLOOP CountOthers > > Increment CountOthersArray[x] > > > > There are several other layers of this program, which is why I'm using > > arrays rather than single variables, but this is the portion I need to > > solve, in order to make the program run faster. > > > > Is this the most straightforward method ? Is there no way to achieve this > > in a single query ? > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ - -- - -------------------------------- Life is poetry - write it in your own words - -------------------------------- Toby Tremayne Code Poet and Zen Master of the Heavy Sleep Senior Technical Consultant Lyricist Software www.lyricist.com.au +61 416 048 090 ICQ: 13107913 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQE/AOBc6KYxVcB16DkRAvUWAKCF9qfMbyIyuE0FZuR0cTsVx8fFZwCdE6pE A+E5vsqiCxruiBWiTcZlyPc= =TAIv -----END PGP SIGNATURE----- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
