I have a report that joining 5 tables:
1 - Coupon Uses: id - coupon_id - user_id - order_id
2 - Coupon: id - code - discount_type - discount_amount
3 - Order: id - user_id - orderread_ts - order_status
4 - Payments: id - user_id - order_id - sub - tax - discount - total - payment_collected
5 - User: id - email - fname - lname
So I'm running a report that takes in several criteria: Promo name, user email, start date, end date.
Here is my query:
SELECT
`Coupon`.`code` AS Promotion,
CONCAT('',o.`id`,'') AS Order_ID,
o.`orderread_ts` AS Order_Date,
u.`email` AS Customer_Email,
CONCAT(u.`fname`, " " ,u.`lname`) AS Name,
p.`subtotal` AS Subtotal,
p.`discount` AS Discount,
p.`total` AS Total
FROM `Coupon`
LEFT JOIN `Coupon_Uses` AS cu ON cu.`coupon_id` = `Coupon`.`id`
LEFT JOIN `Order` AS o ON o.`id` = cu.`order_id`
LEFT JOIN `User` AS u ON u.`id` = o.`user_id`
LEFT JOIN `Payments` AS p ON p.`id` = o.`id`
WHERE `Coupon`.`code` = 'email10'
AND o.`orderread_ts` > 0
AND o.`order_status` = ''
AND p.`discount` > 0
AND p.`payment_collected` = '1'
GROUP BY o.`id`
I'm getting the correct results, however, if during checkout, the sale wasn't complete (bad payment, wrong cc entry, etc.) the table Coupon Uses
will have two entries for the same Order_id
.
Now, the order, when counted will be show incorrect information.
What I can't figure out is how to get distinct order_id
from the coupon_uses
table.
UPDATED FINAL QUERY
SELECT
c.`code` AS Promotion,
o.`id` AS Order_ID,
o.`orderread_ts` AS Order_Date,
u.`email` AS Customer_Email,
CONCAT(u.`fname`, " " ,u.`lname`) AS Name,
p.`subtotal` AS Subtotal,
p.`discount` AS Discount,
p.`total` AS Total
FROM `Coupon` c
INNER JOIN (SELECT DISTINCT coupon_id, user_id, order_id
FROM `Coupon_Uses`) AS cu ON cu.coupon_id = c.id
INNER JOIN `Order` AS o
ON o.`id` = cu.`order_id`
INNER JOIN `User` AS u
ON u.`id` = cu.`user_id`
INNER JOIN `Payments` AS p
ON p.`order_id` = cu.`order_id`
WHERE c.`code` = 'email10'
AND o.`orderread_ts` > 0
AND o.`order_status` = ''
AND p.`discount` > 0
AND p.`payment_collected` = '1'
First change what the I used SELECT DISTINCT
in the first join to make sure i'm getting only unique values. I joined each other table on the fields from coupon_uses
, and I had an error in syntax on the last join, which is now fixed.
For performance: 683 rows affected, taking 30.1ms