Kiran Kiran - 4 months ago 6
SQL Question

Is it possible to put an <if> Condition in a select Query

mysql> select * from categories ;
+-------------+--------------------+-----------------+---------------------
| category_id | T1 | T2 | T3
+-------------+--------------------+-----------------+---------------------
| 2 | Popcorn | Bucket | NULL
| 3 | Popcorn | Jumbo | NULL
| 3 | Popcorn | Jumbo | NULL
6 | Popcorn | Combo Relish | NULL |
7 | Soft Drinks | Fountain | Apple |
7 | Soft Drinks | Fountain | Apple |
8 | Soft Drinks | Fountain | Orange |
8 | Soft Drinks | Bottled | Orange |


I have a table called categories as shown above .

For Some T2 Values the T3 can be Null or can have Value .

I have got a query as shown below

select t1, t2,
group_concat(concat(t3,'(',category_id,')')) consildated_Data
from categories
group by 1,2;


With this the results looks like this

Popcorn | Bucket | NULL
Popcorn | Combo Relish | NULL
Popcorn | Jumbo | NULL
Soft Drinks | Fountain | Apple(7),Apple(7),Orange(8)
Soft Drinks | Bottled | Orange(8)


My question is that if T3 is
null
, can i have the value printed as empty with corresponding
category_id
number ??

so that it looke like this for Bucket

Popcorn | Bucket | empty(2)
Popcorn | Jumbo | empty(3),empty(3)


Is it possible to put a if condition under the above query ??

Answer

Without testing, you can use an IF statement like this:

SELECT
    t1,
    t2,
    IF(
        t3 IS NULL,                                  -- condition
        CONCAT('empty(', category_id, ')'),          -- true reaction
        GROUP_CONCAT(CONCAT(t3,'(',category_id,')')) -- false reaction
    ) AS consildated_Data
FROM categories
GROUP BY 1, 2;
Comments