Report of coupon and users

Question

I have users who are partners and users who are clients. When clients submit an order, they use a custom coupon code associated with each partner. (Each partner has a Mycodes field set within their profile.)

Previously, at the end of the month, we would download the sales into an Excel sheet and calculate the commissions of each partner using the partner's coupon codes.

Now I want to do this on the website using a view. I created a view for the coupons but I cannot compare the coupon code against the partner's Mycodes field. I have written the sql query in phpMyAdmin (one line with two JOIN), but I can not put that into views and filter that based on current userID:

SELECT
  uc_order_line_items.order_id AS OrderID,
  uc_orders.order_status AS Currently,
  uc_orders.created AS ThisDate,
  uc_orders.delivery_first_name AS Name,
  uc_orders.delivery_last_name AS Surname,
  uc_orders.delivery_phone AS Phone,
  uc_orders.primary_email AS Email,
  field_data_field_mycodes.entity_id AS UserID,
  uc_orders.order_total AS Total
FROM
  uc_order_line_items
JOIN
  field_data_field_mycodes
ON
  uc_order_line_items.title = field_data_field_mycodes.field_mycodes_value
JOIN
  uc_orders
ON
  uc_orders.order_id = uc_order_line_items.order_id
WHERE
  field_data_field_mycodes.entity_id=

Answer 1

Thanks to Chris I tried that with 3 different base tables, but none worked. This might not be the answer but it is for the people who might read this article as an option. After spending more than 2 days, I decided to go with the craziest approach yet "doable": I created a login table in the database, wrote one login page and one report page in PHP (less than 650 lines of codes with all those sort and filter), and I was done in about 5 hours. Link to that login went into Drupal, and that was it. Drupal is a very great tool but I guess Views needs to add a free-hand SQL query, and, also a free-hand javascript insertion per page. these could really save programmers a lot saving hours of work...