samedi 2 juillet 2016

MYSQL another statement on WHERE clause if value not null

I want to select field which end_item is null, but if end_item NOT NULL, I want to run another statement in WHERE clause to compare if end_item valid or expired

I tried several code but it only displayed item with not null value, the null value not displayed, I don't want to use UNION statement to select both null or compare the not null end_item here is my code :

SELECT mall.id_mall,jns.nm_jenis,mall.nm_item
,mall.hrg_item,mall.kd_stat,icon.icon_item FROM `db_website`.tb_itemmall mall,`db_website`.tb_icon_item
 icon,`db_website`.tb_jenis jns WHERE IFNULL(mall.end_item, ('2016-07-02 18:20:07'>=mall.end_item mallrt_item OR '2016-07-02 18:20:07' <= mall.end_item)) AND mall.id_item=icon.id_item AND mall.kd_jenis=jns.kd_jenis
 ORDER
 BY id_mall DESC LIMIT 0 ,10

I also try using case but same

SELECT mall.id_mall,jns.nm_jenis,mall.nm_item
,mall.hrg_item,mall.kd_stat,icon.icon_item FROM `db_website`.tb_itemmall mall,`db_website`.tb_icon_item
 icon,`zen_website`.tb_jenis jns WHERE mall.id_item=icon.id_item AND mall.kd_jenis=jns.kd_jenis AND
 (CASE WHEN mall.end_item IS NOT NULL OR mall.end_item != '' THEN ('2016-07-02 18:20:07'>=mall.start_item OR '2016-07-02 18:20:07' <= mall.end_item) END)
 ORDER
 BY id_mall DESC LIMIT 0 ,10

table design

Aucun commentaire:

Enregistrer un commentaire