SELECT mall_product.pf_id, 販売連結用買取データ.kaitori_id_show, 販売連結用買取データ.sku, 販売連結用買取データ.price,mall_product.list_price_original,lng_product_free.free_value AS rank, mskr.hanbai_status AS rank_sns, 商品情報ステータス_日本語, mall_product.商品情報_日本語, mall_product.商品情報_タグ FROM mall_shop_pfstart INNER JOIN mall_sites ON mall_shop_pfstart.shop_id = mall_sites.shop_id INNER JOIN mall_product ON mall_shop_pfstart.shop_id = mall_product.shop_id AND mall_sites.group_id = mall_product.group_id INNER JOIN lng_product ON mall_product.pf_id = lng_product.pf_id INNER JOIN mall_mgr_property_external ON mall_product.mgr_short_name = mall_mgr_property_external.short_name INNER JOIN mall_dept_prod ON mall_product.pf_id = mall_dept_prod.pf_id INNER JOIN lng_dept ON mall_dept_prod.dept_id = lng_dept.dept_id INNER JOIN mall_dept_property ON lng_dept.dept_id = mall_dept_property.dept_id AND mall_dept_prod.dept_id = mall_dept_property.dept_id INNER JOIN lng_super_dept ON mall_dept_property.super_dept_id = lng_super_dept.super_dept_id INNER JOIN mall_product_period ON mall_product.pf_id = mall_product_period.pf_id INNER JOIN lng_product_free ON mall_product.pf_id = lng_product_free.pf_id INNER JOIN lng_shop_free ON lng_shop_free.free_id = lng_product_free.free_id INNER JOIN lng_product_free lpf8 ON mall_product.pf_id = lpf8.pf_id AND lpf8.free_id=8 INNER JOIN lng_product_free lpf4 ON mall_product.pf_id = lpf4.pf_id AND lpf4.free_id=4 LEFT OUTER JOIN 販売連結用買取データ ON mall_product.pf_id = 販売連結用買取データ.pf_id LEFT OUTER JOIN mall_sns_kaitori_rireki mskr ON 販売連結用買取データ.kaitori_id_show = mskr.rireki_id WHERE (lng_product.language = 'eng') AND (lng_dept.language = 'eng') AND (lng_super_dept.language ='eng') AND (mall_shop_pfstart.pf_start <> 'PC') AND (mall_product.group_id = '96') AND (mall_product.list_price_original<>0) AND (mall_product.flag_del = 0) and lng_shop_free.free_id = 2 AND (PATINDEX('%-homepage%', mall_product.pf_id) = 0) and mall_product.pf_id = 'CKaat1780' ORDER BY mall_product.date_changed desc