Wednesday, August 28, 2013

SQL : CASE statements

Using conditions in SQL statements is really useful. Specially when we need to compare 1 value based on conditions with multiple columns.

For example I have 1 table called tasks. Where I need to check the given date parameter should be greater than or equal to multiple columns based on the current tasks category.

I can easily do that by code. Can be ROR or PHP. But if we can get the data directly from database it gets really fast for any language.

In this situation CASE statements are really useful.

In this example I need to compare parameter date to start_date column if category is 'appointment' and to end_date if category is 'todo'.
SELECT * FROM tasks 
WHERE '2013-09-02' >=
CASE 
  WHEN tasks.category='appointment' THEN (date(start_date))
  WHEN tasks.category='todo' THEN (date(end_date))
END

No comments:

Post a Comment