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
        JOIN vendors                                                    ON ( promotions.vendor_id = vendors.id AND vendors.status_id = 1 AND promotions.start_date <= CURDATE() )
        JOIN rewards                                                    ON ( promotions.reward_id = rewards.id                                                                  )
        JOIN rewards_vendor_types_jn            AS rewards_categories   ON ( rewards.id = rewards_categories.reward_id                                                          )
        JOIN vendor_types                       AS categories           ON ( rewards_categories.vendor_type_id = categories.id                                                  )
        LEFT JOIN clients_promotions                                    ON ( promotions.id = clients_promotions.promotion_id                                                    )
        LEFT JOIN clients                                               ON ( clients_promotions.client_id = clients.id                                                          )
        LEFT JOIN geo_marketings                AS marketings           ON ( promotions.id = marketings.marketable_id AND marketings.marketable_type = 'Promotion'              )
        LEFT JOIN geo_market_zipcodes           AS zipcodes             ON ( marketings.market_id = zipcodes.market_id                                                          )


WHERE
        AND promotions.start_date IS NOT NULL
        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
        vendors.name ASC
        , promotions.price ASC
        , rewards.offer ASC
        , clients.name ASC