Help with a join?

Evening all,

I want to do a join on two tables to output the latest statistics for a specific order

I have First table called orders like so.

It holds an order name with a specific order ID, which is never duplicated in this table

I also have another table which holds the statistics for a specific order, and each week the statistics (impressions and clicks) are updated with a row of new data for the order. Here is that table:

Now what I want to do is query both tables to get out the order ID, order name and LATEST (impressions and clicks) for a SINGLE order.

However with the query below it outputs the same order three times when I only want ONE order outputted with the LATEST (impressions and clicks) .



SELECT orders.order_id, orders.campaign_name
       stats.order_id, stats.impressions, stats.clicks 
FROM orders 
INNER JOIN stats ON orders.order_id = stats.order_id 
ORDER BY orders.order_id DESC 

Anyone please help?

sometimes, joining to a derived table subquery performs better than using a correlated subquery in the WHERE clause

try this and see if it EXPLAINs differently –


SELECT o.order_id
     , o.campaign_name
     , s.impressions
     , s.clicks 
  FROM orders AS o
INNER 
  JOIN ( SELECT order_id
              , MAX(period_start) AS latest
           FROM stats 
         GROUP
             BY order_id ) AS m
    ON m.order_id = o.order_id
INNER
  JOIN stats AS s 
    ON s.order_id = m.order_id 
   AND s.period_start = m.latest
ORDER 
    BY o.order_id DESC

You can try using a subquery to select the latest row in the stats table:

SELECT 
   o.order_id, 
   o.campaign_name,
   s.impressions, 
   s.clicks 
FROM 
   orders o
INNER JOIN 
   stats s ON 
      o.order_id = s.order_id AND s.period_start = 
       (SELECT MAX(period_start) FROM stats WHERE order_id = o.order_id)
ORDER BY 
   o.order_id DESC

I like it!

Thanks Joool