Might try IN instead of EXISTS ( so NOT IN) Rob
On Thu, Apr 17, 2008 at 3:09 AM, Mike Little <[EMAIL PROTECTED]> wrote: > just wondering if anyone can help - i am in a bit of a pickle with this > one. > > mike > > > hey guys, > > > > have the following query (which works in SQL Server)... > > > > SELECT child.* > > FROM categories AS parent, categories AS child > > WHERE parent.cat_id = <cfqueryparam cfsqltype="cf_sql_integer" > > value="#arguments.cat_id#"> > > AND child.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt > > AND NOT EXISTS ( > > SELECT * > > FROM categories AS P > > WHERE P.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt > > AND child.cat_lft BETWEEN P.cat_lft AND P.cat_rgt > > AND P.cat_id NOT IN (child.cat_id) > > AND P.cat_id <> <cfqueryparam cfsqltype="cf_sql_integer" > > value="#arguments.cat_id#"> > > ) > > ORDER BY child.cat_lft > > > > for the life of me i cannot workout how to get it to work for a MS > > Access db. i am wondering if access supports subqueries? > > > > can anyone shed some light on this for me? > > > > cheers > > mike > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:303681 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
