Yeah, you're completely missing the point. You're explicitly relying on CF's interpretation of null to be a non-numeric simple value (i.e. the empty string), rather than actually checking to see if the field is null (which is what you should be doing). Either way, you're testing to see if some field returned from the DB is the empty string, and assuming that the empty string means that the field was actually NULL.
I'm not going to argue that CF is wrong; CF's strength is in how it abstracts complexity into simple things, and this is a good example. It's just kind of frustrating that you can't do it the "right" way if you want to. cheers, barneyb On 5/13/05, Ben Rogers <[EMAIL PROTECTED]> wrote: > > SELECT user.firstname AS parent, child.firstname AS child > > FROM user > > LEFT OUTER JOIN user child ON user.userID = child.parentID > > > > If 'child' is the empty string, does the user not have a child, or is > > the child's record incomplete, perhaps because it wasn't given a name > > yet (as is not uncommon for the first day after birth). Hard to tell > > if you don't get back a null. > > > > Yes, it's a contrived example, but it illustrates the point. > > In this situation, I would check the key of the child record: > > SELECT > user.firstname AS parent, > child.firstname AS child, > child.id AS childID > FROM user > LEFT OUTER JOIN user child > ON user.userID = child.parentID > > And then: > > <cfif isNumeric(childID)> > <!--- has child ---> > </cfif> > > It seems like that's a much more accurate test then checking to see if one > of the fields in the joined table is null. > > Let me know if I'm missing the point here. > > Ben Rogers > http://www.c4.net > v.508.240.0051 > f.508.240.0057 -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). CFCDev is supported by New Atlanta, makers of BlueDragon http://www.newatlanta.com/products/bluedragon/index.cfm An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
