CODEWITHSUNDEEP
phpmysql
BardsWork
BardsWork

Reputation: 566

MYSQL: distinct values in join - price is double counted

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

Upvotes: 2

Views: 100

Answers (1)

trincot
trincot

Reputation: 350147

If I understand correctly you can have the situation where you have two records in the Coupon_Uses table which only differ in their id value. coupon_id, user_id, order_id could be the same...

If that is so, I would really suggest to look into that, as it doesn't seem right. Could you not write such a duplicate record in another table during the transaction, so you can ensure there are no such duplicates? Or could you not add a status column so the temporary record gets a Draft status, while all others are Final?

Anyway, if this is the situation, you can solve it as follows in the query:

Replace:

LEFT JOIN `Coupon_Uses` AS cu ON cu.`coupon_id` = `Coupon`.`id`

with:

LEFT JOIN (SELECT DISTINCT coupon_id, user_id, order_id
           FROM `Coupon_Uses`) AS cu ON cu.`coupon_id` = `Coupon`.`id`

But this could have a performance impact, as the join can probably not benefit from using an index.

Upvotes: 2

Related Questions