Database Modeling: query for comparison shopping site

Say I am building a comparison shopping site. This means I have multiple vendors selling the same item. Assume a basic database structure like the following:

==================================
vendor_product
==================================
vendor_product_id (PK)
condition ('new', 'used', 'collectible')
unit_price


==================================
product_mapping
==================================
vendor_product_id
product_id


==================================
product
==================================
product_id (PK)
product_code
name

Given a product_code (or list of product_code), return all vendor products that map to the appropriate product, grouping the vendor products by the mapped product, displaying price range (lowest to highest, regardless of condition), and number of vendors selling the product. The output would be something like this:

======================================================================
product_code | name    | vendor_count | lowest_price | highest price |
======================================================================
 abc123开发者_JAVA百科      | Sony TV | 5            | 200          | 350           |
----------------------------------------------------------------------
 xyz987      | Barbie  | 11           | 15           | 22            |
----------------------------------------------------------------------

I'm not sure how to do the SQL for this. Any suggestions?

---

something roughly like this... not tested at all.

SELECT p.product_code product_code
     , p.name name
     , COUNT(vp.vendor_product_id) vendor_count
     , MIN(vp.unit_price) lowest_price
     , MAX(vp.unit_price) highest_price
 FROM product p
 JOIN product_mapping pm ON p.product_id = pm.product_id
 JOIN vendor_product vp ON pm.vendor_product_id = vp.vendor_product_id
 GROUP BY p.product_code, p.product_name