0

i have table called my_table like this 

+------------+-------+-------+
|  product   | type1 | type2 |
+------------+-------+-------+
| Banana     | NULL  | A1    |
| Grape      | NULL  | B1    |
| Watermelon | A     | A1    |
| Orange     | B     | A1    |
+------------+-------+-------+

i want to select product based on his type, if type1 NULL then the type using type 2, if not then use type 1

so the expected results like this

+------------+------+
|  product   | type |
+------------+------+
| Banana     | A1   |
| Grape      | B1   |
| Watermelon | A    |
| Orange     | B    |
+------------+------+
18Man
  • 572
  • 5
  • 17

1 Answers1

2

Use COALESCE:

SELECT
    product,
    COALESCE(type1, type2) AS type
FROM yourTable;

The COALESCE(a, b) function will return a, should it be not NULL, otherwise it would fallback to returning b.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i want to ask sir, if type 1 and type 2 comes from tbl1 (for type1) and tbl2 (for type2) is it true i wrote SELECT product, COALESCE(tbl1.type1, tbl2.type2) AS type FROM yourTable, tbl1, tbl2; ? – 18Man Apr 17 '20 at 06:37
  • @FachryDzaky Yes, that looks correct and valid to me. – Tim Biegeleisen Apr 17 '20 at 06:42