Forum Moderators: open
SELECT itemmaster_list.*, category_lists.cid as categoryID, category_lists.displayed_text as categoryName, brand_lists.cid as brandID, brand_lists.displayed_text as brandName, brand_lists.image1 as brandImage, brand_lists.cid_text as brandTextCode,
CASE WHEN items_specialprices.itemprice IS NULL
THEN itemmaster_list.default_price
ELSE items_specialprices.itemprice
END as finalprice
FROM itemmaster_list, category_lists, brand_lists
LEFT JOIN items_specialprices ON items_specialprices.itemcode = itemmaster_list.cid
AND items_specialprices.username = 'UserS'
WHERE itemmaster_list.status = 'ACTIVE' AND itemmaster_list.cid = '99' AND category_lists.cid = itemmaster_list.categorycode AND brand_lists.cid = itemmaster_list.brandcode