Line 1:     select
     2:     a.id_listing,
     3:     a.reg_no as listing_reg_no,
     4:     a.id_category,
     5:     if (
     6:     /* 'Apartment / Condo (Highrise)' */
     7:     a.id_category = 2010,
     8:     if (
     9:     a.id_objective = 2000,
    10:     concat(
    11:     ap42.itm_desc, ', ', if( a.holding = 'MALAY_RESERVE', 'Malay Reserve', if( a.holding = 'CUSTOMARY_LAND', 'Customary Land', a.holding ) ), ', ', if( ( a.holding <> 'MALAY_RESERVE' and a.holding <> 'CUSTOMARY_LAND' ),  concat( ap43.itm_desc, ', ' ), '' ), a.position
    12:     ),
    13:     concat(
    14:     ap42.itm_desc, ', ', a.position
    15:     )
    16:     ),
    17:     if (
    18:     /* 'House / Bungalow (Landed)' */
    19:     a.id_category = 2011,
    20:     if (
    21:     a.id_objective = 2000,
    22:     concat(
    23:     ap42.itm_desc, ', ', a.storey, '-storey, ',  if( a.holding = 'MALAY_RESERVE', 'Malay Reserve', if( a.holding = 'CUSTOMARY_LAND', 'Customary Land', a.holding ) ), ', ', if( ( a.holding <> 'MALAY_RESERVE' and a.holding <> 'CUSTOMARY_LAND' ),  concat( ap43.itm_desc, ', ' ), '' ), a.position
    24:     ),
    25:     concat(
    26:     ap42.itm_desc, ', ', a.storey, '-storey, ', a.position
    27:     )
    28:     ),
    29:     if (
    30:     /* 'Office / Shop (Commercial)' */
    31:     a.id_category = 2012,
    32:     if (
    33:     a.id_objective = 2000,
    34:     concat(
    35:     ap42.itm_desc, ', ', a.storey, '-storey, ', if( a.holding = 'MALAY_RESERVE', 'Malay Reserve', if( a.holding = 'CUSTOMARY_LAND', 'Customary Land', a.holding ) ), ', ', if( ( a.holding <> 'MALAY_RESERVE' and a.holding <> 'CUSTOMARY_LAND' ),  concat( ap43.itm_desc, ', ' ), '' ), a.position
    36:     ),
    37:     concat(
    38:     ap42.itm_desc, ', ', a.storey, '-storey, ', a.position
    39:     )
    40:     ),
    41:     if (
    42:     /* 'Land' */
    43:     a.id_category = 2013,
    44:     concat(
    45:     ap42.itm_desc, ' ', ap41.itm_desc, ', ', if( a.id_lot_type = 2080, 'Malay Reserve', if( a.holding = 'MALAY_RESERVE', 'Malay Reserve', if( a.holding = 'CUSTOMARY_LAND', 'Customary Land', a.holding ) ) )
    46:     ),
    47:     'Room'
    48:     )
    49:     )
    50:     )
    51:     ) as category_type_title_holding_lottype_storey,
    52:     if (
    53:     a.id_objective = 2000,
    54:     if (
    55:     a.id_objective_sub = 2008,
    56:     'NEW PROJECT',
    57:     'FOR SALE'
    58:     ),
    59:     'FOR RENTAL'
    60:     ) as modus,
    61:     a.status,
    62:     ifnull( a.publish_dt, '' ) as publish_dt,
    63:     a.asking_price,		a.asking_price_previous,
    64:     a.asking_rental,	a.asking_rental_previous,
    65:     if (
    66:     a.id_objective = 2000,
    67:     a.asking_price,
    68:     a.asking_rental
    69:     ) as price,
    70:     if (
    71:     a.id_objective = 2000,
    72:     a.asking_price_previous,
    73:     a.asking_rental_previous
    74:     ) as price_previous,
    75:     a.diff_market_value_vs_asking_price,
    76:     if (
    77:     trim( ifnull( a.diff_market_value_vs_asking_price, '' ) ) <> '',
    78:     if (
    79:     a.diff_market_value_vs_asking_price < 0,
    80:     'Y',
    81:     'N'
    82:     ),
    83:     'N'
    84:     ) as below_market,
    85:     a.room,				a.bathroom,			a.exclusive,
    86:     a.built_size,		a.built_size_unit,
    87:     a.land_size,		a.land_size_unit,
    88:     a.owner_ads_title as ads_title,
    89:     unix_timestamp( ifnull(b.inf_mod_dt, b.inf_cre_dt) ) as listing_photo_timestamp,
    90:     b.file_path as listing_photo,
    91:     (
    92:     select count(*) as kira1 from lis_listing_file a1 where a1.id_listing = a.id_listing
    93:     ) as listing_photo_count,
    94:     a.id_ren as agent_id,
    95:     if (
    96:     trim( ifnull( d.nickname, '' ) ) <> '',
    97:     d.nickname,
    98:     d.name
    99:     ) agent_name,
   100:     d.ren_level as agent_ren_level,		d.reg_no as agent_reg_no,
   101:     d.photo as agent_photo,				d.mobile as agent_mobile,
   102:     d.user_name as agent_user_name,
   103:     unix_timestamp( ifnull(d.inf_mod_dt, d.inf_cre_dt) ) as agent_timestamp,
   104:     ap1.itm_desc as state,
   105:     ap2.itm_desc as area
   106:     from lis_list c
   107:     left outer join lis_listing a on a.id_listing = c.id_listing
   108:     left outer join lis_listing_file b on b.id_listing = a.id_listing and b.file_name = 'FilePhoto01'
   109:     left outer join neg_agent d on d.id_ren = c.id_ren
   110:     left outer join sys_param ap1 on ap1.itm_id = a.addr_id_state
   111:     left outer join sys_param ap2 on ap2.itm_id = a.addr_id_area
   112:     left outer join sys_param ap41 on ap41.itm_id = a.id_category
   113:     left outer join sys_param ap42 on ap42.itm_id = a.id_type
   114:     left outer join sys_param ap43 on ap43.itm_id = a.id_lot_type
   115:     where c.inf_data_status > 100
   116:     and (
   117:     c.type = 'OWN'
   118:     or
   119:     (
   120:     c.type = 'COMARKET'
   121:     and (
   122:     c.status = 'REQUEST' or c.status = 'AGREE'
   123:     )
   124:     )
   125:     )
   126:     and a.inf_data_status > 100
   127:     and a.publish = 'Y'
   128:     and ifnull(a.id_project, '') = ''
   129:     and a.status = 'AVAILABLE'
   130:     and b.file_name = 'FilePhoto01'
   131:     and ifnull(a.id_objective_sub, '') <> '2008'
   132:     and ifnull(a.publish_expired_dt, '') <> ''
   133:     and a.publish_expired_dt >= ?
   134:     and c.id_ren = ?
   135:     and a.id_objective = ?
   136:     order by publish_dt desc
   137:     limit 0,