If you do a left outer join between table1 and table2, this will give you all occurrences of table1, and only those records that match in table2. Where there is no matching table2 record, it will be returned as nulls, which CF will interpret a an empty string.
SQL Server help on left outer joinn is... USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC Here is the result set: au_fname au_lname pub_name -------------------- ------------------------------ ----------------- Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems (23 row(s) affected) The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column. ----- Original Message ----- From: "Andrew Dickinson" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2003 9:29 AM Subject: [cfaussie] Is Null, take 2 > 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/ > --- 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/
