-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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/AN/06KYxVcB16DkRAjVnAJ427yeHviPkaO6U1jtFEJOqiV1eIgCcDj5k
ZfKMsM8NyOx1+DKaXmovrkM=
=RbXN
-----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/
