Mysql – Query an intermediate table and join to child table

MySQL

Here's my database structure:

  • Product
  • Market
  • ProductMarket (linked to Product and Market with "product_id" and "market_id"
  • Price (linked to ProductMarket with "productmarket_id")

I want to query the ProductMarket table and filter by a particular product. Then, I want to associate the latest price (from the Price table) to the product (for each market), based on a date field in the Price table.

Right now, I have that:

SELECT
    mp.id AS id,
    p.name AS productName, 
    m.name AS marketName, 
    mp.identifier
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

I'm having difficulty integrating the Price table in. How can I achieve this?

Best Answer

This sounds like a job for a correlated subquery.

SELECT
    mp.id AS id,
    p.name AS productName, 
    m.name AS marketName, 
    mp.identifier,
    (SELECT the_price_column 
       FROM price pr
      WHERE pr.marketproduct_id = mp.id  
      ORDER by the_date_column DESC LIMIT 1) as latest_price
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

Your question said "productmarket" but the query you posted used "marketproduct" so I went with the latter in the example.

If the price table has no prices for a particular product, this query will return null for the price of that product.

The price table needs a multi-column index on (marketproduct_id, the_date_column) to optimize this query.

Update: You can get other columns, too, by repeating the subquery and asking for something different... 

...
    m.name AS marketName, 
    mp.identifier,
    (SELECT the_price_column 
       FROM price pr
      WHERE pr.marketproduct_id = mp.id  
      ORDER by createddate DESC LIMIT 1) as latest_price,
    (SELECT createddate 
       FROM price pr
      WHERE pr.marketproduct_id = mp.id  
      ORDER by createddate DESC LIMIT 1) as latest_date
FROM
    marketproduct mp
...

Or, you could try it with a join, moving the subquery to a join condition in the where clause to get as many columns as you need from the price table.

SELECT
    mp.id AS id,
    p.name AS productName, 
    m.name AS marketName, 
    mp.identifier,
    pr.the_price_column,
    pr.createddate
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
INNER JOIN
    price pr ON pr.id = 
    (SELECT id 
       FROM price prx
      WHERE prx.marketproduct_id = mp.id  
      ORDER by createddate DESC LIMIT 1)
WHERE
    productName = 'SomeProduct';

Both of these feel slightly messy, because in the first example, we're dipping into the price table twice, and in the second example, we're referencing the price table twice with two different aliases ... but it's hard to see a way to avoid that. The performance of the two queries should be fairly comparable, though. In both cases, having to go twice isn't as bad as it sounds because whichever element hits the table first will have already brought the index and row data into the buffer pool (InnoDB) or OS/key cache (MyISAM) so the penalty for the second hit on the same row shouldn't be significant.

Related Question