Zaffar Saffee Zaffar Saffee - 2 years ago 93
MySQL Question

INNER JOIN on 2 tables returns wrong values

Here is my


SELECT SUM(amz_event_shipment_items.quantity),

FROM amz_event_shipment_items

INNER JOIN amz_event_fees ON = amz_event_fees.shipment_item_id
INNER JOIN amz_shipment_events ON = amz_event_shipment_items.shipment_event_id

WHERE amz_event_fees.currency = 'USD'
AND amz_shipment_events.event_type <> 'RefundEvent'
AND amz_shipment_events.posted_date BETWEEN '2016-5-1 07:00:00' AND '2016-5-7 06:59:59'

GROUP BY amz_event_shipment_items.seller_sku

But the returned values are too high... Can't make sense to me...

Am I missing anything?


Many shipment_events for each date

Each shipment_event HAS MANY shipment_item / BELONGS TO ONE event

Each shipment_item HAS MANY shipment_fee / BELONGS TO ONE item

Answer Source

You are multiplying the quantities by the number of fees. Use an IN or EXISTS clause when looking for mere existence.

from amz_event_shipment_items i
where exists
  select *
  from amz_event_fees f
  where f.currency  = 'USD'
  and f.shipment_item_id =
and exists
  select *
  from amz_shipment_events e
  where e.event_type <> 'RefundEvent'
  and e.posted_date between '2016-05-01 07:00:00' and '2016-05-07 06:59:59'
  and = i.shipment_event_id
group by i.seller_sku;

(MySQL is known to be slow on IN clauses sometimes, so I am using EXISTS here, although I like IN better.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download