Back

Post Detail

Home

I see a lot of 60 line queries these days but I also see simple mistakes being made. I first want to state that this is not directed towards you Phil, your queries are god like. So let's get back to some basics here. I see many occurrences where people will write a query to get the first and last name of a contact and then concatenate the two as full name and return the full name of the contact. Let us all remember the string concatenation is possible through SQL statements as well. The following examples will work with MySql & MSSQL respectively. I am not sure about oracle or any other database for that matter because I just don't use them. I hope this is not too basic and someone learns something from this. I will be writing more back to basic posts in the coming weeks, if no one reads them it's fine at least I have a reference point. Ha!!!

Mysql


<cfquery name="getFullName" datasource="#application.dsn#">
select CONCAT(fname, ' ',lname) As fullname
FROM contact;
</cfquery>

MSSQL


</p><p><cfquery name="getFullName" datasource="#application.dsn#">
select fname+ ' '+lname AS fullname
FROM contact
</cfquery>

Comments

Jacob Munson's Gravatar Jacob Munson
July 18, 2006 9:25 AM

I appreciate why you are writing this back to basics stuff, because I too have seen some bad queries. I'm not a SQL guru, but I do know that sometimes there are legitimate reasons to have 60 line queries. If you are working with a properly normalized database, and you've got to do some complex calculations, or you just plain have a lot of columns to grab, your queries can grow very quickly. For the standard web stuff that's just displaying 3-5 fields from 2-3 tables, I agree that 60 lines is usually overkill.

I just re-read your post, and it looks like you might not have been saying that 60 line queries are bad. But I still stand by my comment. :)

dan's Gravatar dan
July 19, 2006 10:32 AM

Thanks for the feedback Jacob. I want you and any other readers to know that I am no SQL guru either. I do think there are many places where a 60 line query full of joins is needed. My post and my example was just to let people know that in some places there are easier ways to accomplish your goals.

Beth's Gravatar Beth
July 26, 2006 11:25 AM

For Oracle:

<cfquery name="getFullName" datasource="#application.dsn#">
select fname || ' ' || lname AS fullname
FROM contact
</cfquery>

dan's Gravatar dan
July 26, 2006 11:28 AM

Thanks for the tip Beth!