Zaffar Saffee Zaffar Saffee - 6 months ago 15
MySQL Question

INNER JOIN on 2 tables returns wrong values

Here is my

SQL
query:

SELECT SUM(amz_event_shipment_items.quantity),
amz_event_shipment_items.seller_sku

FROM amz_event_shipment_items

INNER JOIN amz_event_fees ON amz_event_shipment_items.id = amz_event_fees.shipment_item_id
INNER JOIN amz_shipment_events ON amz_shipment_events.id = 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?

Edit

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

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

select 
  sum(i.quantity), 
  i.seller_sku
from amz_event_shipment_items i
where exists
(
  select *
  from amz_event_fees f
  where f.currency  = 'USD'
  and f.shipment_item_id = i.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 e.id = 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.)