MySQL: SQL Query to return two different minimums

Question

I have 3 MySQL tables:

items
id      |  title
 1      |  The Matrix DVD
 2      |  Star Wars DVD


itemListings
// condition_id 1 = New, 2 = Used
id  |item_id| condition_id
 1  |   2   |   2 
 2  |   2   |   1
 3  |   2   |   1
 4  |   2   |   1
 5  |   2   |   2
 6  |   2   |   2
 8  |   2   |   2
 9  |   1   |   1   


itemListings_inventory
id  | listing_id | start_price
 1  |   1        |   4.00
 2  |   2        |  12.00
 3  |   3        |  14.00
 4  |   4        |  15.00
 5  |   5        |   6.00
 6  |   6        |   7.00
 7  |   8        |  11.00
 8  |   8        |   9.00
 9  |   8        |   2.00
10  |   8        |  13.00
11  |   9        |   5.00

If the user searches for "star wars", I need to return:

item_id |  title        | lowest_new_price   | lowest_used_price 
   2    | Star Wars DVD |   12.00            |       2.00

So far I have this SQL which returns just the lowest_used_price:

SELECT 
items.id,
items.title,
MIN(start_price) AS lowest_used_price
FROM itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items.id
WHERE itemListings.condition_id = 2
AND MATCH (items.title, items.description) AGAINST ('star wars') 
GROUP BY items.id

but how could I get lowest_new_price and lowest_used_price?

UPDATE: Some items will have both a new price and a used price, some items will have just a new price and some items will have just a used price.

Answer 1

Group by solutions are preferable to me for being somewhat more general. I'd do something like this:

SELECT x.id, x.title, SUM(IF(x.conditionId = 1, x.price, 0)) lowest_new_price, SUM(IF(x.conditionId = 2, x.price, 0)) lowest_used_price
 (  SELECT i.id, i.title, iL.conditionId, MIN(iLI.start_price) price  FROM items i, itemListings iL, itemListings_inventory iLI
     WHERE i.title = 'Star Wars'
       AND i.id = iL.id
       AND iL.id = iLI.listing_id 
  GROUP BY i.id, i.title, iL.conditionId) x
GROUP BY i.id, i.title

The conditional sums pick out the minimum price of the conditon_id groupings from the subquery.

Answer 2

Thought exercise:

Your itemlistings table, it holds old and new items, right? And your query is essentially how you get the minimum used and the minimum new price..

Could you do it if the used items were in one table, and the new items were in another table (so two separate listings tables instead of your one)

Probably..

So the answer, then, is to conceptually split that listings table into two, by putting it into your query twice, once for new, once for old. This is easiest to conceive by use of subqueries:

SELECT * FROM
Items
INNER JOIN
(
 SELECT 
  ItemListings.id,
  MIN(start_price) AS lowest_used_price
 FROM 
  itemListings_inventory
  JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
 WHERE itemListings.condition_id = 2
 GROUP BY items.id 
)used
ON items.id = used.id

INNER JOIN
(
  SELECT 
   items.id,
   MIN(start_price) AS lowest_new_price
  FROM 
   itemListings_inventory
   JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
  WHERE itemListings.condition_id = 1
  GROUP BY items.id
) nu
ON items.id = nu.id

You can also do it via a method called pivoting, where you join it once, but use conditionals to pick out only rows that are interesting, then group them up:

SELECT 
  items.id,
  items.title,
  MIN(CASE WHEN condition_id = 2 THEN start_price END) AS lowest_used_price,
  MIN(CASE WHEN condition_id = 1 THEN start_price END) AS lowest_new_price
FROM 
  itemListings_inventory
  JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
  JOIN items ON itemListings.item_id = items.id

GROUP BY items.id, items.title

To see more of how it works, run this:

SELECT items.id, items.title,
  CASE WHEN condition_id = 2 THEN start_price END AS used_price,
  CASE WHEN condition_id = 1 THEN start_price END AS new_price
FROM 
  itemListings_inventory
  JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
  JOIN items ON itemListings.item_id = items

And know that MIN picks the lowest non null value

Note, I haven't put any WHERE item.id = 2 or WHERE items.title LIKE '%star wars%' (I.e. Only the Star Wars rows) in these queries; I figured you could do that part yourself (and in any case it's unlikely you want to hard code Star Wars as the only kind of DVD your code searches); this is more about running through the basic technique of joining a table in twice if it has things you want to appear on the same result row, though the table has them on different rows.. and also about the other technique of using CASE WHEN to reduce uninteresting rows to null, so that MIN will ignore them..

Answer 3

It can be more efficient, but this should work.

SELECT new.id, new.title, new.lowest_new_price, used.lowest_used_price
FROM (
    SELECT 
    items.id,
    items.title,
    MIN(start_price) AS lowest_new_price
    FROM itemListings_inventory
    JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
    JOIN items ON itemListings.item_id = items.id
    WHERE itemListings.condition_id = 1
    AND MATCH (items.title, items.description) AGAINST ('star wars') 
    GROUP BY items.id
) new
JOIN (
    SELECT 
    items.id,
    items.title,
    MIN(start_price) AS lowest_used_price
    FROM itemListings_inventory
    JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
    JOIN items ON itemListings.item_id = items.id
    WHERE itemListings.condition_id = 2
    AND MATCH (items.title, items.description) AGAINST ('star wars') 
    GROUP BY items.id
) used ON used.id = new.id