SQL: How do I select one of two fields, depending on a third field

Question

I need to export data from an existing TABLE. Till recently I used -

SELECT item_ID, item_Info, ...moreFields... FROM tableName WHERE myCondition=0

Now, they changed the TABLE structure, and added new field "item_Info2"

When they fill in the TABLE:

• if "item_Type" is 1 or 2 or 3 or 4, then "item_Info" is relevant
• if "item_Type" is 5, then "item_Info" is empty, and I need "item_Info2" as my query result, instead of "item_Info"

What is the corresponding SELECT command?

[similiar question: mysql select any one field out of two with respect to the value of a third field

but from this example I cannot see the syntax for selecting moreFields ]

Thanks,

Atara.

Answer 1

You can treat the CASE statement as any other column name, separate it from the other columns with a comma, etc. What happens in the CASE should be considered a single column name, in your case you need commas before and after it.

SELECT item_ID, CASE WHEN item_Type = 5 THEN item_info ELSE item_info2 END, field_name, another_field_name ...moreFields... FROM tableName WHERE myCondition=0

you could also use an alias to be easier to get the result from the query:

SELECT item_ID, CASE WHEN item_Type = 5 THEN item_info ELSE item_info2 END AS 'item_info', field_name, another_field_name ...moreFields... FROM tableName WHERE myCondition=0