OK, I'm not a beginner with SQL. After 7 years of hammering away at web apps I've picked up a little bit here and there. But I also try to remember how difficult it was for me to wrap my head around certain things that didn't necessarily have to do with programming logic. Things like design, layout, and database calls.

Yesterday I was working on an old template at work. It was a self posting add/edit form, with case statements in the beginning to determine what particular form action was to be taken. This particular form handled user administration and administrative access privileges within the same form, tying admin users to ticket groups within our ticketing system.

So, here's the scenario for you. You have a table of user data, within which certain users are marked as admin users. You have another table that maps admin users to ticket groups (administration, sales, support, etc.) Knowing this you might assume that a query to fill the form might look something like this:

view plain print about
1<cfquery name="VARIABLES.GetAdminUsers" datasource="#APPLICATION.myDSN#">
2 SELECT U.intUserID,
3 U.vcFirstName,
4 U.vcLastName,
5 mUTG.bIsTicketQueueManager,
6 mUTG.bIsGroupManager
7 FROM Users U
8 INNER JOIN mapUsersTicketGroups mUTG ON U.intUserID = mUTG.fkUsers
9 WHERE U.bIsAdministrator = 1
10</cfquery>

That seems fairly straight forward, right? OK, let's throw a kink into it. Lets say that users (even admin users) might not be assigned to a ticket group. The above query would only return users that are part of a ticket group, so we have to adjust our query to bring in all admin users. We do this by changing our JOIN type:

view plain print about
1<cfquery name="VARIABLES.GetAdminUsers" datasource="#APPLICATION.myDSN#">
2 SELECT U.intUserID,
3 U.vcFirstName,
4 U.vcLastName,
5 mUTG.bIsTicketQueueManager,
6 mUTG.bIsGroupManager
7 FROM Users U
8 LEFT JOIN mapUsersTicketGroups mUTG ON U.intUserID = mUTG.fkUsers
9 WHERE U.bIsAdministrator = 1
10</cfquery>

Now we're getting somewhere. A record is returned for each admin user, regardless of whether they have been assigned to a ticket group. But this is where it gets trickier still. Remember, you have a form that is populated by this query. All of the columns related to ticket group information for users who are unassigned to a ticket group will return a NULL value. This is important because ColdFusion converts database NULLs to an empty string the first time they are referenced as a simple value:

view plain print about
1<input type="checkbox" name="bIsTicketQueueManager" id="bIsTicketQueueManager"<cfif VARIABLES.GetAdminUsers.bIsTicketQueueManager> checked="checked"</cfif> value="1" />
2<input type="checkbox" name="bIsGroupManager" id="bIsGroupManager"<cfif VARIABLES.GetAdminUsers.bIsGroupManager> checked="checked"</cfif> value="1" />

On records containing NULLs, the above code would throw an error, because "" can not be cast to a boolean value. If only we could set a default value for fields that might return NULL? You could write some conditional statements after your query to set values, but that gets clunky and places the burden on your ColdFusion server processes.

Luckily most SQL servers (except MS Access, but it doesn't count) have built in functions to handle just this type of task directly at the server level. With MS SQL server you would use the ISNULL(expr1,expr2) method. MySQL uses the similar IFNULL(expr1,expr2) method, while within PostgreSQL you can apparently do this with the COALESCE(expr1,expr2) method. All of these work in the same basic way, evaluating expression one and, if NULL, applying expression two (there are slight differences, see links for documentation [p.s. Read documentation comments too]). So, to fix our earlier query we could do something like this:

view plain print about
1<cfquery name="VARIABLES.GetAdminUsers" datasource="#APPLICATION.myDSN#">
2 SELECT U.intUserID,
3 U.vcFirstName,
4 U.vcLastName,
5 ISNULL(mUTG.bIsTicketQueueManager,0) AS bIsTicketQueueManager,
6 ISNULL(mUTG.bIsGroupManager,0) AS bIsGroupManager
7 FROM Users U
8 LEFT JOIN mapUsersTicketGroups mUTG ON U.intUserID = mUTG.fkUsers
9 WHERE U.bIsAdministrator = 1
10</cfquery>

Wonderful little trick to use, relatively simple, and can be used in many different ways. Default date values, booleans, foriegn key id's, whatever you might need. Took me a few years to find this (didn't know what I was looking for), so I hope it helps somebody.