> 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
----------------------------------------------------------
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]
