A practical CASE Statement Example
Published on Mar 4, 2009 by Jamie MunroWhat 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:
CASE v WHEN 2 THEN SELECT v; WHEN 3 THEN SELECT 0; ELSE BEGIN END; END CASE;
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!