select distinct h.*, s.sort_no, s.spa_cd, s.spa_nm, s.spa_kana, s.info1, p.pref_cd, p.pref_nm, p.sort_no, p.spa_link_flg from mst_hotel h left outer join mst_spa s on h.spa_cd = s.spa_cd left outer join mst_pref p on s.pref_cd = p.pref_cd left outer join mst_hotel_plan_type pt on h.hotel_cd = pt.hotel_cd left outer join mst_plan_type mpt on pt.plan_type_cd = mpt.plan_type_cd left outer join mst_plan pln on (h.hotel_cd = pln.hotel_cd and pln.disp_flg = '1') left outer join mst_plan_plan_link lnk on (pln.hotel_cd = lnk.hotel_cd and pln.plan_no = lnk.plan_no) left outer join mst_plan_charge_title ttl on (pln.hotel_cd = ttl.hotel_cd and pln.plan_no = ttl.plan_no) where h.disp_flg = '1' and p.pref_cd = '21' and (p.pref_nm like '%�ԥ󥯥���ѥ˥���%' or s.spa_nm like '%�ԥ󥯥���ѥ˥���%' or s.spa_kana like '%�ԥ󥯥���ѥ˥��ï¿?%' or h.hotel_nm like '%�ԥ󥯥���ѥ˥���%' or h.hotel_kana like '%�ԥ󥯥���ѥ˥��ï¿?%' or h.address like '%�ԥ󥯥���ѥ˥���%' or h.access like '%�ԥ󥯥���ѥ˥���%' or h.catch like '%�ԥ󥯥���ѥ˥���%' or h.inv_comment like '%�ԥ󥯥���ѥ˥���%' or h.notice like '%�ԥ󥯥���ѥ˥���%' or h.recom1 like '%�ԥ󥯥���ѥ˥���%' or h.gooddeal like '%�ԥ󥯥���ѥ˥���%' or (h.notice <> '' and 'Åö´Û¤Î¿··¿¥³¥í¥Ê¥¦¥¤¥ë¥¹´¶À÷¾ÉÂкö¤Ë¤Ä¤¤¤Æ' like '%�ԥ󥯥���ѥ˥���%') or pln.plan_nm like '%�ԥ󥯥���ѥ˥���%' or mpt.plan_type_nm like '%�ԥ󥯥���ѥ˥���%' or pln.rsv_gift like '%�ԥ󥯥���ѥ˥���%' or pln.remarks like '%�ԥ󥯥���ѥ˥���%' or pln.free_drink like '%�ԥ󥯥���ѥ˥���%' or pln.keyword like '%�ԥ󥯥���ѥ˥���%' or pln.capture like '%�ԥ󥯥���ѥ˥���%' or pln.benefits like '%�ԥ󥯥���ѥ˥���%' or pln.add_item like '%�ԥ󥯥���ѥ˥���%' or ttl.charge_nm like '%�ԥ󥯥���ѥ˥���%' ) order by h.sort_no_all, p.sort_no, s.sort_no, s.spa_cd, h.sort_no, h.hotel_cd