Follow @endyourif rss Facebook LinkedIn

A practical CASE Statement Example A practical CASE Statement Example

Published on Mar 4, 2009

What is a SQL case statement? Well, it's much like a switch statement in a development language. It allows you to test several different cases for a field and determine what you would like to do for each one.

If you have ever visited Mysql's documentation, you may have been as lost as I was. You will find something like this:


If you are as confused as I was, let me provide you a better, more clear example.

Let's break down what's happening above.  It's analyzing the variable "v".  When v is equal to 2, it will return "v", if it's 3, it will return "0", otherwise it will do nothing.

Let's apply this to more realistic example.  Let's say we have a state field and a country field, but we only want to show the state if the country is USA.  We could write something like this:

SELECT first, last, country,
CASE country WHEN 'USA' THEN state ELSE 'Outside USA' END as state
FROM users

In the above example, if our country is USA it will return the actual state, otherwise it will return Outside USA.  Hopefully this provides a clear idea of where this can be used.  I will now should a much more complex example.  This is something I have used to create a message center that displays the most recent message in the summary.

SELECT m1.* FROM messages m1
LEFT JOIN messages m2 ON
 CASE WHEN m1.idparent = 0 THEN
  CONCAT(m1.idmessage, CHAR(45), m1.idparent) ELSE m1.idparent END = m2.idparent
  AND m1.idmessage < m2.idmessage
WHERE m2.idmessage IS NULL
AND m1.receiver_id = 1
ORDER BY m1.datesent DESC

The following query will return all messages for receiver_id = 1.  By joining the messages table to itself it will allow us to retrieve the most recent content to display.

Let's analyze this more.  First off, we our doing a left join because not all messages will have children messages (or a reply), so if nothing is found it will return the main record.

Our actual JOIN condition takes care of ensuring it returns us the most recent content.  It does this by doing a case statement.  If the parent id is 0 this means it is the first record in the thread, to ensure it's a unique and won't return every other first record in the thread we concatenate the message id and the parent id together separated by a "-".  Otherwise, if the message is a child we join it to all of the other child threads.  We then proceed to ensure that the message id being returned is less than the highest message id.

Hopefully the above ramble is clear, it's sometimes hard to explain this kind of logic and hopefully it will enable you to successfully use CASE statements in your future work!

Tags: SQL | case | concat | SQL

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP