Thanks, I was able to figure it out... just a left outer join and checking for nulls that are returned from the left outer. Thanks for looking!
-----Original Message----- From: Brian Kotek [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 3:42 PM To: CF-Talk Subject: Re: sql not in You can also use WHERE NOT EXISTS. I'd try to write an example for you but I don't really understand what you're trying to do aside from the fact that you want to select something from table 1 when it doesn't exist in table 2. Are "a,b,c,d" column names? Data values? The way you described this is too abstract. A more concrete example would be more helpful. Regards, Brian On 8/9/07, Peterson, Chris <[EMAIL PROTECTED]> wrote: > > SELECT > A,b,c,d > FROM > tbl1 > WHERE > A NOT IN (SELECT distinct A from tbl2) > > > Or you could: > > SELECT > A.a, > A.b, > A.c, > A.d > FROM > tbl1 A EXCEPTION JOIN tbl2 B ON A.a = B.a > > This would return any non-matching records where tbl1.A has no matching > tbl2.A > > > Chris Peterson > Gainey IT > Adobe Certified Advanced Coldfusion Developer > > -----Original Message----- > From: Tim Do [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 09, 2007 2:37 PM > To: CF-Talk > Subject: OT: sql not in > > how do I return values from tbl1 where values are NOT in tbl2. > > > > tbl1 > > a,b,c,d > > > > tbl2 > > a,b,c,d > > > > only a is passed in. b,c,d are unique. tbls do not have identity > fields. > > > > thanks, > > tim > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade to ColdFusion 8 and integrate with Adobe Flex http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:285928 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
