mysql IF Else Statement

Question

Not sure how far a sql query can go with if/else statements.

I have a simple SELECT statement:

SELECT amount, transtype FROM transactions

The transtype column is going to be a number.

For example, 1 = sale, 2 = refund, 3 = error, 4 = canceled, 5 = something else.... and so on.

So, nothing complicated. But the list tends to grow for reporting reasons. Which is fine.

For a specific query I'm working on, is there a way to extract that column as one of 2 or three specified numbers or text?

For example, some transtype numbers are a 'loss', while others are a 'gain', and maybe others are 'neutral'.

I'd like to extract that column with only those 3, without using php inside the html table I'm throwing the rows into.

If my explanation is not clear, my apologies. It was hard to spit out.

Solution 1

Use the MySQL CASE() function for a fixed number of arguments. If the list is getting big, you should use a second table and join them.

Example:

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

Solution 2

Try joining against another table that contains the transaction types. Something like:

TRANSACTION_TYPES
transtype | number
label     | varchar(32)

Then modify your query to include the join:

select t.amount, t.transtype, l.label 
    from transactions.t 
    join transaction_types l on t.transtype = l.transtype;

Comments

[coffeemonitor]

I was trying to stay away from the extra table. I don't foresee the transtype growing more than 10 numbers. But you're right, that's a quick solution.

[Joe Mastey]

It will also preserve the relational integrity of the database, so that you can make sense of it without the specific query to "explain" your magic numbers for you.