The query below (and the original one) doesn't say "not where both are blank", it says "where both are not blank" which is very different. You are after:
SELECT * FROM myTABLE WHERE NOT ((before is NULL or before = '') and (after is NULL or after = '')) and database_id = #url.id# On 7/23/07, Les Mizzell <[EMAIL PROTECTED]> wrote: > Mike Kear wrote: > > If i recall, "<>'' "' is not the same as "is not NULL". So perhaps > > you need to change your query to something like: > > > > select * from myTABLE > > WHERE > > before is not NULL > > and after is not NULL > > and someID = #url.id# > > Which would see to lead me to this: > > SELECT * FROM myTABLE > WHERE > (before is not NULL or before <> '') > and (after is not NULL or after <> '') > and database_id = #url.id# > > But this returns zero results, and there's clearly cases where, "after" > has a value, so I' still not sure what I've got wrong. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284342 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4