SELECT DISTINCT p.pf_id, p.product_coin, p.list_price_original/100 AS price, p.list_price_original, p.qty_min, p.inventory, p.date_entered, p.date_changed, p.type, p.mgr_short_name , p.img_path, lp.name, lp.product_copy, lp.product_no, ld.dept_name AS shop_name, ld.dept_id AS shop_id, lsd.super_dept_id, lsd.super_dept_name FROM mall_product p INNER JOIN lng_product lp ON p.pf_id = lp.pf_id INNER JOIN mall_dept_prod mdp ON p.pf_id = mdp.pf_id INNER JOIN lng_dept ld ON mdp.dept_id = ld.dept_id INNER JOIN mall_dept_property mdpp ON mdpp.dept_id = mdp.dept_id INNER JOIN lng_super_dept lsd ON mdpp.super_dept_id = lsd.super_dept_id INNER JOIN lng_product_free lpf ON lpf.pf_id = p.pf_id INNER JOIN mall_product_period mpp ON p.pf_id = mpp.pf_id WHERE p.inventory > 0 and p.flag_del =0 AND (mpp.sale_on = 1 and ( mpp.sale_start < GETDATE() or mpp.sale_start is null ) and ( mpp.sale_end > GETDATE() or mpp.sale_end is null ) ) and ld.dept_id in (1646,1313,1315,1328,1330,1308,1283,1287,1292,1254,1655,1656,1243,1244,1234,1204,1206,1208,1210,1212,1214,1216,1218,1220,1222,1224,1226,1228,1506,1494,1485,1487,1434,1431,1427,1401,1403,1405,1409,1415,1417,1419,1421,1391,1448,1452,1458,1468,1270,1272,1273,1258,1267,1268,1260,1261,1263,1264,1265,1256,1381,1383,1369,1375,1377,1286,1303,1275,1288,1291,1293,1312,1314,1316,1236,1388,1416,1420,1422,1424,1394,1396,1402,1404,1368,1370,1376,1378,1380,1382,1320,1321,1322,1323,1324,1325,1326,1327,1329,1331,1333,1249,1203,1205,1207,1209,1211,1213,1215,1217,1219,1221,1223,1225,1227,1229,1231,1351,1352,1353,1354,1355,1346,1348,1336,1338,1340,1342,1344,1357,1358,1359,1364,1367,1441,1428,1433,1455,1471,1474,1505,1486,1512,1543,1514,1509,1535,1526,1527,1528,1546,1579,1580,1581,1582,1583,1577,1575,1571,1557,1560,1552,1564,1565,1566,1603,1590,1597,1598,1592,1593,1609,1615,1616,1716,1711,1703,1701,1601,1695,1639,1671,1672,1661,1733,1735,1736,1746,1747,1754,1756,1777,1778,1779,1769,1770,1758,1767,1768) AND (p.list_price <> 0) AND (SUBSTRING(p.pf_id, 1, 2) <> 'PC') AND (PATINDEX('%-homepage%', p.pf_id) = 0) ORDER BY p.date_changed desc