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.

Answer 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;

Answer 2

You should probably use ENUM type for that column. It's limited to 64 values, however if you would need more then you should create a new table and JOIN it in query.

Answer 3

The ELT function should do the trick:

SELECT ELT(`transtype`, 'loss', 'loss', 'gain', 'neutral', …) FROM …

Not very elegant though, and logically I would do this in the view logic, not the database logic.

Answer 4

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;