Ever had the need to insert a record, but only wanted to do so if the record doesn't already exist? Typically the first approach to this is two queries: one to check for the existence of the values you want to insert, the second to peform that insert IF the first query returned no records. Well, I was in such a spot today, but hated the idea of having to hit the db twice to perform a conditional insert. Though I'm sure I'm not the first one to come up with this approach, I thought I'd share it in case it helps someone else, too.

I'm using a MySQL database, so the query I'm going to demonstrate is written a little bit MySQL-specific, though it is easily translated into MSSQL-speak as well.

The Scenario

Okay, we're looping through a file where each line represents a record. For each of those records, we want to perform an insert IF that record doesn't already exist. Here's how I'm doing it:

First of all, please know that I highly recommend the use of <cfqueryparam> tags in every place that you see a variable being output. I ommitted that tag in this example for clarity's sake.

Walking through the code, first thing I'm doing is grabbing my values from the current file line. Next, the query. It is obviously an INSERT query using a select statement. The select statement is simply selecting the actual raw values, but ONLY if a record meeting that exact criteria does not already exist. If no record is selected in the WHERE's subquery, the values are inserted; otherwise, nothing happens at all.

Notice my use of the "LIMIT" attribute. This performs the equivalent action of DISTINCT, ensuring that only a single record is returned. Without limiting the results of the SELECT statement, we would get multiple results and would be inserting massive amounts of duplicates. For MSSQL users, simply add the DISTINCT keyword to your SELECT statement, as in "SELECT DISTINCT '#carrier#', '#claimnum#','#priorclaim#' FROM ...."

The Scenario

You're writing code to perform authentication for a web-based app. Your users live in one table (Site_User), your list of site permissions live in another table (systemRoles), and the two are related via a junction table (jctUserRole) that manages the many to many relationship there. Your backend database: MySQL version 5 or better.

So, in an ideal world, you will be able to write a single query that will both authenticate the user AND retrieve a comma delimited list of their roles contained in a single field; if their credentials are good, your result will only have a single record in it. Typically, however, because of the way many to many relationships are normalized, the "one query" you want will be returning multiple rows...one row for each permission the authenticating user is related to. For example, here is the typical way one would write the authenticating query:

This produces a result that looks like this:

We would then be required to write additional code in order to more properly "package" up this user's list of permissions for use within the system.

But enter the wonderful world of MySQL 5, and the 'group_concat' function. Group_concat allows you to concatenate values into a list based on a specified grouping, and will produce a list of unique values delimited by whatever character you choose (but defaults to a comma). So transforming the above query just a little bit, we get:

And a result set that now looks like this:

Man, SO much cleaner and nicer, eh? Now you can simply treat the "roles" value as a list...explode it into an array if you want, or leverage ColdFusion's myriad of List functions to interact with it.

Since discovering group_concat, I've found several other uses for it as well, particularly when producing reports for my internal clients...it saves me a lot of iterating over result sets in order to produce value summaries.

You can read the documentation for this function here