1

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?

2

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.

  • Super, it's working! Is there a way to get the date (createddate) too back from my query? – jpmonette Oct 17 '13 at 0:51
  • 1
    Excellent. Updated the answer to show two options for fetching additional columns. – Michael - sqlbot Oct 17 '13 at 2:31
  • The example repeating the subquery does work, but the second one was returning me two rows for the same marketproduct. At least, it's working :) – jpmonette Oct 17 '13 at 22:06
  • That last part is surprising. I have reviewed the logic and don't see how his query could return more rows than the others, although I do see that it would eliminate the rows with no price, where the queries with the subquery(-ies) in the SELECT list would have returned null values for their columns. – Michael - sqlbot Oct 18 '13 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.