Mysql IF Else Statement

Question

Not sure how far sql can work with if / else statements.

I have a simple SELECT statement:

SELECT amount , transtype FROM transactions

The transtype column will be a number.

For example, 1 = sale, 2 = return, 3 = error, 4 = canceled, 5 = something else .... etc.

So, nothing complicated. But the list is usually growing for reporting reasons. What well.

For the particular query I'm working on, is there a way to extract this column as one of two or three specified numbers or text?

For example, some transistor numbers are “loss,” others are “gain,” and others are “neutral.”

I would like to extract this column with only these 3, not using php inside the html table. I throw the lines in.

If my explanations are not clear, my apologies. It was hard to spit it out.

Answer 1

Use the MySQL CASE () function for a fixed number of arguments. If the list becomes large, you should use the second table and join it.

Example:

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

Answer 2

Try joining another table containing transaction types. Something like:

 TRANSACTION_TYPES transtype | number label | varchar(32) 

Then modify your request to enable the connection:

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

Answer 3

The ELT function should do the trick:

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

Not very elegant, although logically I would do it in the presentation logic, and not in the database logic.

Answer 4

You should probably use the ENUM type for this column. It is limited to 64 values, however, if you need more, you must create a new table and JOIN in the query.