SELECT  SQL_CALC_FOUND_ROWS 1 as availableInLocation, 
COALESCE(uwlp.uwlp_selprod_id, 0) as is_in_any_wishlist, 
prodcat_code, 
product_id, 
prodcat_id, 
COALESCE(product_name, product_identifier) as product_name, 
product_model, 
product_updated_on, 
COALESCE(prodcat_name, prodcat_identifier) as prodcat_name, 
selprod_id, 
selprod_user_id, 
selprod_code, 
selprod_stock, 
selprod_condition, 
selprod_price, 
COALESCE(selprod_title  ,COALESCE(product_name, product_identifier)) as selprod_title, 
splprice_display_list_price, 
splprice_display_dis_val, 
splprice_display_dis_type, 
splprice_start_date, 
splprice_end_date, 
brand_id, 
COALESCE(brand_name, brand_identifier) as brand_name, 
user_name, 
IF(selprod_stock > 0, 1, 0) AS in_stock, 
selprod_sold_count, 
selprod_return_policy, 
selprod_min_order_qty, 
IF(((selprod_free_shipping > 0) OR ((theprice > shop_product_free_shipping) && (shop_enable_free_shipping>0))), 1, 0) AS selprod_free_shipping, 
theprice, 
special_price_found
 FROM 
`tbl_products` p  
LEFT OUTER JOIN `tbl_products_lang`  AS tp_l  
 ON productlang_product_id = p.product_id AND productlang_lang_id = 3  
INNER JOIN `tbl_seller_products`  AS msellprod  
 ON msellprod.selprod_product_id = p.product_id and selprod_deleted = 0 and selprod_active = 1  
LEFT OUTER JOIN `tbl_seller_products_lang`  AS sprods_l  
 ON msellprod.selprod_id = sprods_l.selprodlang_selprod_id AND sprods_l.selprodlang_lang_id = 3  
INNER JOIN (SELECT pmp_product_id, 
pmp_selprod_id, 
pmp_min_price as theprice, 
pmp_max_price as maxprice, 
pmp_splprice_id, 
if(pmp_splprice_id,1,0) as special_price_found
 FROM 
`tbl_products_min_price` )  AS pricetbl  
 ON pricetbl.pmp_product_id = msellprod.selprod_product_id and msellprod.selprod_id = pricetbl.pmp_selprod_id  
LEFT OUTER JOIN `tbl_product_special_prices`  AS msplpric  
 ON msplpric.splprice_selprod_id = pricetbl.pmp_selprod_id and pricetbl.pmp_splprice_id = msplpric.splprice_id  
INNER JOIN `tbl_users`  AS seller_user  
 ON selprod_user_id = seller_user.user_id and seller_user.user_is_supplier = 1 AND seller_user.user_deleted = 0  
INNER JOIN `tbl_user_credentials`  AS seller_user_cred  
 ON credential_user_id = seller_user.user_id and credential_active = 1 and credential_verified = 1  
INNER JOIN `tbl_shops`  AS shop  
 ON seller_user.user_id = shop.shop_user_id  and shop.shop_active = 1 and shop.shop_supplier_display_status = 1  
LEFT OUTER JOIN `tbl_shops_lang`  AS s_l  
 ON shop.shop_id = s_l.shoplang_shop_id AND shoplang_lang_id = 3  
INNER JOIN `tbl_countries`  AS shop_country  
 ON shop.shop_country_id = shop_country.country_id and shop_country.country_active = 1  
INNER JOIN `tbl_states`  AS shop_state  
 ON shop.shop_state_id = shop_state.state_id and shop_state.state_active = 1  
LEFT OUTER JOIN `tbl_brands`  AS brand  
 ON p.product_brand_id = brand.brand_id    
LEFT OUTER JOIN `tbl_brands_lang`  AS tb_l  
 ON brand.brand_id = tb_l.brandlang_brand_id AND brandlang_lang_id = 3  
INNER JOIN `tbl_product_to_category`  AS ptc  
 ON ptc.ptc_product_id = p.product_id  
INNER JOIN `tbl_product_categories`  AS c  
 ON c.prodcat_id = ptc.ptc_prodcat_id and c.prodcat_active = 1 and c.prodcat_deleted = 0  
LEFT OUTER JOIN `tbl_product_categories_lang`  AS c_l  
 ON c_l.prodcatlang_prodcat_id = c.prodcat_id AND prodcatlang_lang_id = 3  
LEFT OUTER JOIN (SELECT uwlp_selprod_id, 
uwlp_uwlist_id
 FROM 
`tbl_user_wish_list_products` uwlp  
INNER JOIN `tbl_user_wish_lists`  AS uwl  
 ON uwl.uwlist_id = uwlp.uwlp_uwlist_id  
WHERE `uwlist_user_id` = '0')  AS uwlp  
 ON uwlp.uwlp_selprod_id = selprod_id  
WHERE `product_active` = '1' 
AND `product_deleted` = '0' 
AND `product_approved` = '1' 
AND shop.`shop_active` = '1' 
AND shop.`shop_supplier_display_status` = '1' 
AND shop_country.`country_active` = '1' 
AND shop_state.`state_active` = '1' 
AND c.`prodcat_active` = '1' 
AND c.`prodcat_deleted` = '0' 
AND c.`prodcat_status` = '1' 
AND c.`prodcat_code` LIKE '000001_000016_000011_000086_%' 
AND `selprod_deleted` = '0' 
GROUP BY product_id 
ORDER BY in_stock DESC, theprice asc 
limit 0, 100