SELECT DISTINCT vendors.name AS vendor, rewards.offer AS reward, promotions.price AS points, promotions.discount_price AS discount_points, promotions.discount_start_date AS
discount_start_date, promotions.discount_end_date AS discount_end_date, promotions.id AS id, vendors.id AS vendor_id, rewards.id AS reward_id, clients.id AS client_id, clients.name AS
client, clients.id AS client_id, zipcodes.zipcode AS zipcode, concat(vendors.id, "-", rewards.id) AS unique_key
FROM promotions
	INNER JOIN vendors ON promotions.vendor_id = vendors.id
	INNER JOIN rewards ON promotions.reward_id = rewards.id
	INNER JOIN rewards_vendor_types_jn AS rewards_categories ON rewards.id = rewards_categories.reward_id
	INNER JOIN vendor_types AS categories ON rewards_categories.vendor_type_id = categories.id
	LEFT OUTER JOIN clients_promotions ON promotions.id = clients_promotions.promotion_id
	LEFT OUTER JOIN clients ON clients_promotions.client_id = clients.id
	LEFT OUTER JOIN geo_marketings AS marketings ON (promotions.id = marketings.marketable_id AND marketings.marketable_type = 'Promotion')
	LEFT OUTER JOIN geo_market_zipcodes AS zipcodes ON marketings.market_id = zipcodes.market_id


WHERE vendors.status_id = 1
	AND promotions.start_date IS NOT NULL
	AND promotions.start_date <= CURDATE()
	AND ( ( (rewards.quantity > 0 OR rewards.unlimited = 1) AND (promotions.end_date IS NULL OR promotions.end_date > CURDATE()) ) OR promotions.display_out_of_stock = 1)
	AND (categories.visible = 1 OR (categories.name = 'Landing' OR categories.name = 'Points Alert' OR categories.name = 'We Suggest' OR categories.name = 'Widget'))
	AND promotions.activation_bonus_reward = false
	AND ((zipcodes.zipcode = '48306') OR (clients.id = 2627 OR clients.id = 2629 OR clients.id = 2501))

	GROUP BY unique_key
	ORDER BY UPPER(vendors.name) ASC, UPPER(promotions.price) ASC, UPPER(rewards.offer) ASC, UPPER(clients.name) ASC