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=